﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Office2010.Excel;
using DocumentFormat.OpenXml.Office2013.Drawing.ChartStyle;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using FFMpegCore.Enums;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
using Microsoft.Extensions.Options;
using Minio;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql;
using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using SixLabors.ImageSharp.ColorSpaces;
using StackExchange.Redis;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.Data.Entity.Core.Metadata.Edm;
using System.Drawing.Printing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading;
using static housingsafety.server.Controllers.VillageMngController;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 房屋管理服务
    /// </summary>
    public class ExportController : BaseController<ExportController>
    {
        public IConfiguration Configuration { get; }
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildings> _buildingsRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsLayerRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdateRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdate;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmPlaceCommercial> _placeCommercialRepository;
        private readonly IRepository<ZmPlaceResident> _placeResidentRepository;
        private readonly IOptions<StaticDataConf> _staticVtOptions;
        private readonly IRepository<ZmDepartment> _departmentRepository;
        private readonly IRepository<RiskCensusHouseYhpc> _riskHouseRepository;
        private readonly IRepository<RiskCensusYhpcUpldFile> _riskHouseFileRepository;
        private readonly IRepository<ZmPlaceResidentCheck> _placeResidentCheckRepository;
        private readonly IRepository<ZmPlaceCommercialCheck> _placeCommercialCheckRepository;
        private readonly IRepository<ZmYtPhoto> _zmYtPhotoRepository;
        private readonly IDbTransaction _transaction;

        private readonly Microsoft.AspNetCore.Http.Features.FormOptions _defaultFormOptions = new Microsoft.AspNetCore.Http.Features.FormOptions();
        private readonly string[] _permittedExtensions = { ".mp3", ".mp4", ".jpg", ".jpeg", ".png", ".geojson", ".doc", ".docx" };

        private static object _lock = new object();

        public ExportController(IStorageProvider storageProvider
            , IRepository<ZmDepartment> departmentRepository
            , IRepository<ZmUser> userRepository
              , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
              , IRepository<ZmBuildings> buildingsRepository
            , IRepository<ZmBuildingsLayer> buildingsLayerRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmPlaceCommercial> placeCommercialRepository
            , IRepository<ZmPlaceResident> placeResidentRepository
            , IRepository<RiskCensusHouseYhpc> riskHouseRepository
            , IRepository<RiskCensusYhpcUpldFile> riskHouseFileRepository
            , IRepository<ZmPlaceResidentCheck> placeResidentCheckRepository
            , IRepository<ZmPlaceCommercialCheck> placeCommercialCheckRepository
            , IRepository<ZmYtPhoto> zmYtPhotoRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , IOptions<StaticDataConf> staticVtOptions
            , ILogger<ExportController> logger,
            IConfiguration configuration
            , IRepository<ZmSystemLog> systemLogRepository, IRepository<ZmUpdateLog> zmupdateRepository) : base(logger, systemLogRepository)
        {
            _zmupdateRepository = zmupdateRepository;
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();
            _zmYtPhotoRepository = zmYtPhotoRepository;
            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _buildingsLayerRepository = buildingsLayerRepository;
            _fileAttachRepository = fileAttachRepository;
            _placeCommercialRepository = placeCommercialRepository;
            _placeResidentRepository = placeResidentRepository;
            _staticVtOptions = staticVtOptions;
            _departmentRepository = departmentRepository;
            _riskHouseRepository = riskHouseRepository;
            _riskHouseFileRepository = riskHouseFileRepository;
            _placeResidentCheckRepository = placeResidentCheckRepository;
            _placeCommercialCheckRepository = placeCommercialCheckRepository;
            Configuration = configuration;
            _villageFireSafetyRepository = villageFireSafetyRepository;
            _zmupdateRepository = zmupdateRepository;

        }
        /// <summary>
        /// 导出人员疏散通道
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/stat/sysstdExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult sysstdExportExcel([FromQuery] string startTime, [FromQuery] string endTime)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }
                if (string.IsNullOrEmpty(startTime)) 
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                string sql = "  select ROW_NUMBER() OVER()index ,* from(select t2.*, ssltdbfjsl, COALESCE(three, 0) three, " +
                "COALESCE(two, 0) two, COALESCE(one, 0) one, COALESCE(three_ywccount, 0) three_ywccount, COALESCE(two_ywccount, 0) two_ywccount," +
                " COALESCE(one_ywccount, 0) one_ywccount," +
                "case  when  ( zcount - ssltdbfjsl )<0 then 0  else zcount - ssltdbfjsl end  wwccount," +
               " CASE    WHEN one_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END one_pencent," +
               " CASE    WHEN two_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END two_pencent," +
               " CASE    WHEN three_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END three_pencent," +
               "     CASE    WHEN ssltdbfjsl = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END dbl_pencent" +
                " from(SELECT  sum(case when tdsfdb= '是' then 1 else 0 end) AS ssltdbfjsl," +
                " sum( CASE WHEN(zm_pc_base.cs >= 3) THEN 1 ELSE 0 END) AS three," +
                " sum( CASE WHEN(zm_pc_base.cs = 2) THEN 1 ELSE 0 END) AS two," +
                " sum( CASE WHEN(zm_pc_base.cs <= 1) THEN 1 ELSE 0 END) AS one," +
                " sum(case when tdsfdb= '是' and cs>= 3 then 1 else 0 end) AS three_ywccount," +
                " sum(case when tdsfdb= '是' and cs = 2 then 1 else 0 end) AS two_ywccount," +
                "    sum(case when tdsfdb= '是' and cs = 1 then 1 else 0 end) AS one_ywccount," +
                "    zm_pc_base.xzq,zm_pc_base.xzqdm FROM" +
                " (SELECT  ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.*,jcfwzt,hcfwzt FROM zm_pc_baselog t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh LEFT JOIN  (select fwbh,fwzt jcfwzt from  zm_pc_base_cj ) t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join  (select fwbh,fwzt hcfwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh " +
                "    WHERE t1.create_on BETWEEN '" + startTime + " 00:00:00' AND '" + endTime + " 23:59:59'  ) zm_pc_base" +
                "    WHERE (create_on) BETWEEN '" + startTime + " 00:00:00' AND '" + endTime + " 23:59:59' AND row_num = 1 AND jcfwzt IS NULL  and hcfwzt is null  " +
                "    GROUP BY zm_pc_base.xzqdm,	zm_pc_base.xzq) t1" +
                "    LEFT JOIN(	 SELECT COUNT(1) AS zcount, zm_buildings.xzq, zm_buildings.xzqdm FROM(SELECT t1.*, t2.fwzt,t3.fwzt hcfwzt FROM zm_buildings t1 LEFT JOIN  (select fwbh,fwzt from  zm_pc_base_cj ) t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join  (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh) zm_buildings WHERE fwzt IS NULL and hcfwzt is null GROUP BY zm_buildings.xzqdm, zm_buildings.xzq ORDER BY zm_buildings.xzqdm   " +
                "    ) t2 ON t1.xzqdm = t2.xzqdm) t where 1 = 1  " + strWhere;



                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("房屋栋数");
                row.CreateCell(3).SetCellValue("人员疏散通道建设达标栋数");
                row.CreateCell(4).SetCellValue("人员疏散通道建设不达标栋数");
                row.CreateCell(5).SetCellValue("达标率");
                row.CreateCell(6).SetCellValue("一层达标率");
                row.CreateCell(7).SetCellValue("二层达标率");
                row.CreateCell(8).SetCellValue("三层及以上达标率");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "ssltdbfjsl")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "wwccount")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "dbl_pencent")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "one_pencent")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "two_pencent")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "three_pencent")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "人员疏散通道建设情况"+DateTime.Now.ToString("yyyyMMdd")+".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }



        /// <summary>
        /// 导出专项信息统计
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/specialbase/SpecialBaseExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult SpecialBaseExportExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            try
            {

                var userNumber = this.CurrentUser();
              
                string strWhere = "";
                if (!string.IsNullOrEmpty(xzqdm + ""))
                {
                    strWhere += "and xzqdm ='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm + ""))
                {
                    strWhere += "and towndm ='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm + ""))
                {
                    strWhere += "and villagedm ='" + villagedm + "'";
                }
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01 ";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                string tablename = "zm_pc_baselog";
                string row_filed = "";
                string row_num_where = "";
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                if (startTime == "2024-01-01" && endTime == today)
                {
                    tablename = "zm_pc_base";
                    row_filed = "";
                    row_num_where = "";
                }
                else
                {

                    DateTime start = Convert.ToDateTime(startTime);
                    DateTime end = Convert.ToDateTime(endTime);
                    
                    row_filed = " ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, ";
                    row_num_where = " and row_num =1 ";
                }

                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }

                string sql = @"select ROW_NUMBER() OVER() index,* from (select mm.xzq,mm.village,mm.town,COALESCE(zcount,0) zcount,COALESCE(zg_ddc_num,0) zg_ddc_num,COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,
COALESCE(ccfhwggptzlsl,0) ccfhwggptzlsl,COALESCE(xggdjsbpds,0) xggdjsbpds,COALESCE(ssltdbfjsl,0) ssltdbfjsl,COALESCE(no_ssltdbfjsl,0) no_ssltdbfjsl,
COALESCE(dbl_pencent,'0%')dbl_pencent,COALESCE(azl_pencent,'0%') azl_pencent from zm_building_ledger mm INNER JOIN (select b4.*,pp.region_name village ,kk.region_name town,COALESCE(xggdjsbpds,0) xggdjsbpds ,
COALESCE(ssltdbfjsl,0) ssltdbfjsl,case when  (zcount-COALESCE(ssltdbfjsl,0)) <=0 then 0 else (zcount-COALESCE(ssltdbfjsl,0)) end  
         no_ssltdbfjsl,case when ssltdbfjsl= 0 then '0.00%' else concat(  CASE WHEN(round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric)
   THEN 100.00 ELSE round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end dbl_pencent,case when xggdjsbpds= 0 then '0.00%' else
     concat( CASE WHEN(round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end azl_pencent 
from (select b1.*,COALESCE(zg_ddc_num,0)zg_ddc_num,COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,
   COALESCE(ccfhwggptzlsl,0)ccfhwggptzlsl from (SELECT count(1) AS zcount,zm_buildings.xzq,zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm  
FROM (select  t1.xzq,t1.xzqdm,t1.towndm,t1.villagedm ,t2.fwzt,t3.fwzt hcfwzt from zm_buildings t1 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh::text = t2.fwbh::text 
LEFT JOIN (select fwbh,fwzt from zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 ON t1.fwbh::text = t3.fwbh::text) zm_buildings  where fwzt is null and hcfwzt is null
GROUP BY zm_buildings.xzqdm, zm_buildings.xzq,zm_buildings.towndm,zm_buildings.villagedm ORDER BY zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm) b1  
left join( select (COALESCE(zc_zg_ddc_num,0)+COALESCE(jc_zg_ddc_num,0)) zg_ddc_num,(COALESCE(zc_zg_yhsyq_num,0)+COALESCE(jc_zg_yhsyq_num,0))zg_yhsyq_num,a.villagedm from  
	(select sum(zg_ddc_num) zc_zg_ddc_num,sum(zg_yhsyq_num) zc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson     a join zm_buildings b on a.fwbh=b.fwbh 
left join zm_pc_base_cj c on b.fwbh=c.fwbh LEFT JOIN (select fwbh,fwzt hcfwzt from zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 ON b.fwbh::text = t3.fwbh::text where fwzt is null and hcfwzt is null and a.create_on between '" + startTime + "  00:00:00' and '" + endTime + " 23:59:59'  GROUP BY b.villagedm) a "
    + "left join (select sum(zg_ddc_num) jc_zg_ddc_num,sum(zg_yhsyq_num) jc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson_cj     a join zm_buildings b on a.fwbh=b.fwbh   "
    + "join zm_pc_yhlog_dx_cj c on a.yhlog_dx_id=c.id join zm_pc_yh_cj d on d.id=c.yhlog_id  where d.jcly='5' and a.create_on between '" + startTime + " 00:00:00' and '" + endTime + " 23:59:59'  GROUP BY b.villagedm)b "
    + "on a.villagedm=b.villagedm) b2	on b1.villagedm=b2.villagedm  left join (select sum(ccfhwggptzlsl) ccfhwggptzlsl,villagedm from zm_pc_gjzz_cjlog where date(create_on)   "
    + "between '" + startTime + "  00:00:00' and '" + endTime + " 23:59:59' group by villagedm) b3 on b1.villagedm=b3.villagedm) b4  left join (SELECT count(DISTINCT CASE WHEN (zm_pc_base.xggdjsbp > 0)  "
    + "THEN zm_pc_base.fwbh ELSE NULL::character varying END) AS xggdjsbpds, sum( CASE WHEN tdsfdb='是' THEN 1 ELSE 0 END) AS ssltdbfjsl,zm_pc_base.xzq,zm_pc_base.xzqdm,zm_pc_base.town, "
    + "zm_pc_base.village FROM (SELECT  " + row_filed + " t1.*       FROM " + tablename + " t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh    "
    + "WHERE t1.create_on BETWEEN '" + startTime + "  00:00:00' AND '" + endTime + " 23:59:59'    )zm_pc_base WHERE    fwbh not in( select fwbh  from  zm_pc_base_cj where fwzt is not null)    AND fwbh not in( select fwbh  from  zm_pc_base_sqjz where fwzt is not null and yhly = '4') "
    + "and fwbh not in (select fwbh from zm_pc_base_sqjz where fwzt is not null and yhly='4')  " + row_num_where + "  GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq,zm_pc_base.town,zm_pc_base.village) b5 on b4.villagedm=b5.village  "
    + "left join zm_department pp on b4.villagedm=pp.id left join zm_department kk on b4.towndm=kk.id order by b4.villagedm ) kk on mm.villagedm=kk.villagedm) t where 1=1  " + strWhere ;



                if (cked == "true")
                {
                    sql = @"select  ROW_NUMBER() OVER() index,* from (select mm.xzq,mm.village,mm.town,COALESCE(zcount,0) zcount,COALESCE(zg_ddc_num,0) zg_ddc_num,
COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,COALESCE(ccfhwggptzlsl,0) ccfhwggptzlsl,COALESCE(xggdjsbpds,0) xggdjsbpds,COALESCE(ssltdbfjsl,0) ssltdbfjsl,
COALESCE(no_ssltdbfjsl,0) no_ssltdbfjsl,COALESCE(dbl_pencent,'0%')dbl_pencent,COALESCE(azl_pencent,'0%') azl_pencent from zm_building_ledger mm INNER JOIN 
(select b4.*,pp.region_name village ,kk.region_name town,COALESCE(xggdjsbpds,0) xggdjsbpds ,COALESCE(ssltdbfjsl,0) ssltdbfjsl,case when 
(zcount-COALESCE(ssltdbfjsl,0)) <=0 then 0 else (zcount-COALESCE(ssltdbfjsl,0)) end  
         no_ssltdbfjsl,case when ssltdbfjsl= 0 then '0.00%' else concat(  CASE WHEN(round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric)
   THEN 100.00 ELSE round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end dbl_pencent,case when xggdjsbpds= 0 then '0.00%' else
     concat( CASE WHEN(round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end azl_pencent 
from (select b1.*,COALESCE(zg_ddc_num,0)zg_ddc_num,COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,
   COALESCE(ccfhwggptzlsl,0)ccfhwggptzlsl from (SELECT count(1) AS zcount,zm_buildings.xzq,zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm  FROM (select  t1.xzq,t1.xzqdm,t1.towndm,t1.villagedm ,fwzt,t1.fwbh from zm_buildings t1 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh::text = t2.fwbh::text) zm_buildings  
where  fwzt is null and fwbh not in( select fwbh  from  zm_pc_base_sqjz where fwzt is not null and yhly='4') GROUP BY zm_buildings.xzqdm, zm_buildings.xzq,zm_buildings.towndm,zm_buildings.villagedm ORDER BY zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm) b1  left join( select (COALESCE(zc_zg_ddc_num,0)+COALESCE(jc_zg_ddc_num,0)) zg_ddc_num,
(COALESCE(zc_zg_yhsyq_num,0)+COALESCE(jc_zg_yhsyq_num,0))zg_yhsyq_num,a.villagedm from  
	(select sum(zg_ddc_num) zc_zg_ddc_num,sum(zg_yhsyq_num) zc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson     a join zm_buildings
b on a.fwbh=b.fwbh left join zm_pc_base_cj c on b.fwbh=c.fwbh where c.fwzt is null and a.fwbh not in( select fwbh  from  zm_pc_base_sqjz where fwzt is not null and yhly='4')   
and a.create_on between '" + startTime + "  00:00:00' and '" + endTime + " 23:59:59'  GROUP BY b.villagedm) a  left join (select sum(zg_ddc_num) jc_zg_ddc_num,sum(zg_yhsyq_num) jc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson_cj     a join zm_buildings b on a.fwbh=b.fwbh  join zm_pc_yhlog_dx_cj c " +
     "on a.yhlog_dx_id=c.id join zm_pc_yh_cj d on d.id=c.yhlog_id  where d.jcly='5' and a.create_on between '" + startTime + " 00:00:00' and '" + endTime + " 23:59:59'  GROUP BY b.villagedm)b on a.villagedm=b.villagedm) b2	on b1.villagedm=b2.villagedm  left join (select sum(ccfhwggptzlsl) " +
        "ccfhwggptzlsl,villagedm from zm_pc_gjzz_cjlog where date(create_on)  between '" + startTime + "  00:00:00' and '" + endTime + " 23:59:59' group by villagedm)" +
        " b3 on b1.villagedm=b3.villagedm) b4  left join (SELECT count(DISTINCT CASE WHEN (zm_pc_base.xggdjsbp > 0) THEN zm_pc_base.fwbh ELSE NULL::character varying END) AS xggdjsbpds, sum( CASE WHEN tdsfdb='是' THEN 1 ELSE 0 END) AS ssltdbfjsl,zm_pc_base.xzq,zm_pc_base.xzqdm,zm_pc_base.town,zm_pc_base.village" +
        " FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.*       FROM " + tablename + " t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh  " +
        " WHERE t1.create_on BETWEEN '" + startTime + "  00:00:00' AND '" + endTime + " 23:59:59'   )zm_pc_base WHERE   row_num =1  AND fwbh not in (SELECT DISTINCT fwbh FROM "+table_fwzt+" WHERE final_fwzt IS NOT NULL AND final_create_on = '"+endTime+" 23:59:59) GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq,zm_pc_base.town,zm_pc_base.village) b5 on b4.villagedm=b5.village " +
        "left join zm_department pp on b4.villagedm=pp.id left join zm_department kk on b4.towndm=kk.id order by b4.villagedm ) kk on mm.villagedm=kk.villagedm)" +
        " t where 1=1 " + strWhere  ;
        }

                    //写入缓存
                    var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("专线信息统计");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("房屋栋数");
                row.CreateCell(5).SetCellValue("清理违规停放电动自行车数量");
                row.CreateCell(6).SetCellValue("清理液化石油气瓶数量");
                row.CreateCell(7).SetCellValue("拆除窗口防护网、广告牌、铁栅栏数量");
                row.CreateCell(8).SetCellValue("安装固定警示标牌（栋）");
                row.CreateCell(9).SetCellValue("安装率");
                row.CreateCell(10).SetCellValue("人员疏散通道建设达标栋数");
                row.CreateCell(11).SetCellValue("人员疏散通道建设不达标栋数");
                row.CreateCell(12).SetCellValue("达标率");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                           // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value+"");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zg_ddc_num")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zg_yhsyq_num")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "ccfhwggptzlsl")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "xggdjsbpds")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "azl_pencent")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "ssltdbfjsl")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "no_ssltdbfjsl")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "dbl_pencent")
                            {
                                row.CreateCell(12).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "专项信息统计情况"+DateTime.Now.ToString("yyyyMMdd")+".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }



        /// <summary>
        /// 导出悬挂警示标牌情况统计
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/xgjsbp/xgjsbpExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult xgjsbpExportExcel()
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";


                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);

                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else  //乡镇
                    {
                        strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                    }
                }

                string today = DateTime.Now.ToString("yyyy-MM-dd");
  
                string table_fwzt = "";

                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;

                string endTime = DateTime.Now.ToString("yyyy-MM-dd");
                table_fwzt = "zm_building_fwzt_" + endData.Year;
                string sql = @"select ROW_NUMBER() OVER() index,* from (select b1.*,xggdjsbpds,zcount-xggdjsbpds no_xggdjsbpds, xggdjsbpcount,jzfjs,case when xggdjsbpds= 0 then '0.00%' else concat(
      CASE WHEN(round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2)  
END, '%') end azl_pencent,case when xggdjsbpcount= 0 then '0.00%' else concat(
      CASE WHEN(round((((xggdjsbpcount)::numeric / (jzfjs)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpcount)::numeric / (jzfjs)::numeric) * (100)::numeric), 2)  
END, '%') end fjzal_pencent from (select xzq,xzqdm,sum(zcount) zcount from pro_hourse_total('" + endTime + " 23:59:59','" + table_fwzt + "') group by xzq,xzqdm) b1 left join" +
    " (select xzq,xzqdm,sum(CASE WHEN xggdjsbp > 0 THEN 1 ELSE 0 END) AS xggdjsbpds from" +
                            " (SELECT t1.* FROM zm_pc_base t1, zm_buildings t2,zm_pc_yh t3 WHERE t1.fwbh = t2.fwbh  and t1.ID = t3.base_id ) t1 where fwbh not in(select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ))  group by xzqdm,xzq) b2 on b1.xzqdm=b2.xzqdm " +
    " left join (select xzq,xzqdm,sum(xggdjsbp) AS xggdjsbpcount,sum(jzfjs) jzfjs from (SELECT t1.* FROM zm_pc_base t1, zm_buildings t2,zm_pc_yh t3 WHERE t1.fwbh = t2.fwbh  and t1.ID = t3.base_id) t1 where fwbh not in(select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' )) group by xzqdm,xzq) b3 on b1.xzqdm=b3.xzqdm) t  " +
    " where 1=1   " + strWhere;
                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("悬挂警示标牌情况统计");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("房屋栋数");
                row.CreateCell(3).SetCellValue("安装固定警示标牌栋数");
                row.CreateCell(4).SetCellValue("安装率");
                row.CreateCell(5).SetCellValue("出租房间数");
                row.CreateCell(6).SetCellValue("安装固定警示标牌个数");
                row.CreateCell(7).SetCellValue("房间安装率");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xggdjsbpds")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "azl_pencent")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "jzfjs")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "xggdjsbpcount")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "fjzal_pencent")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "悬挂警示标牌情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出充电方式统计
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/cdfs/cdfsExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult cdfsExportExcel()
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";


           

           
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
              
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else  //乡镇
                    {
                        strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                    }
                }

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";

                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;


                string sql = @"select ROW_NUMBER() OVER() index,* from (select xzq,xzqdm,
					 sum(CASE WHEN name = '单插口' THEN COALESCE(count,0) else 0 END) AS dchak,
					 sum(CASE WHEN name = '集中充电桩' THEN COALESCE(count,0) else 0  END) AS jzcdz,
					 sum(CASE WHEN name = '单仓口' THEN COALESCE(count,0) else 0  END) AS dcangk,
					 sum(CASE WHEN name = '充(换)电柜'  THEN COALESCE(count,0) else 0  END) AS cdg,
					 sum(CASE WHEN name = '其他'  THEN COALESCE(count,0) else 0  END) AS qt
					from (SELECT split_part(cdfs, '|', 1) AS name,COUNT(*) AS count,xzq,xzqdm FROM  zm_pc_base  where fwbh not in(select distinct fwbh from "+ table_fwzt + " where final_fwzt is not null and   final_create_on='"+today+" 23:59:59')         GROUP BY name,xzq,xzqdm) t group by xzq,xzqdm order by xzqdm) t where 1=1 " + strWhere;

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("充电方式统计");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("单插口");
                row.CreateCell(3).SetCellValue("集中充电桩");
                row.CreateCell(4).SetCellValue("单仓口");
                row.CreateCell(5).SetCellValue("充(换)电柜");
                row.CreateCell(6).SetCellValue("其他");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "dchak")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "jzcdz")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "dcangk")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "cdg")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "qt")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "充电方式统计" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出消防设施
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/xfss/cdfsExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult xfssExportExcel()
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else  //乡镇
                    {
                        strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                    }
                }
                string sql = @"select ROW_NUMBER() OVER() index,* from (select xzq,xzqdm, sum(sfszszxhs) sfszszxhs,sum(xfctdsl)xfctdsl,sum(abzjsxfscsl) abzjsxfscsl,sum(pzxfsb) pzxfsb,sum(jsswxhs) jsswxhs,sum(sfjszzxfdhxxxfz)sfjszzxfdhxxxfz,sum(sfjswxxfz)sfjswxxfz,sum(xfctdslxz)xfctdslxz from  zm_pc_gjzz_cj group by xzq,xzqdm order by xzqdm) t where 1=1  " + strWhere ;

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("充电方式统计");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("消防车通道数量");
                row.CreateCell(3).SetCellValue("设置市政消火栓数量");
                row.CreateCell(4).SetCellValue("按标准建设消防水池数量");
                row.CreateCell(5).SetCellValue("配置消防水泵数量");
                row.CreateCell(6).SetCellValue("建设室外消火栓数量");
                row.CreateCell(7).SetCellValue("建设专职消防队或小型消防站数量");
                row.CreateCell(8).SetCellValue("建设微型消防站");
                row.CreateCell(9).SetCellValue("新增消防车通道数量");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "xfctdsl")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "sfszszxhs")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "abzjsxfscsl")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "pzxfsb")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "jsswxhs")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "sfjszzxfdhxxxfz")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "sfjswxxfz")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            else if (kvp.Key == "xfctdslxz")
                            {
                                row.CreateCell(9).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "消防设施统计" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出充电设施配比情况
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/cdsspbqk/cdsspbqkExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult cdsspbqkExportExcel([FromQuery] string startTime, [FromQuery] string endTime)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else  //乡镇
                    {
                        strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                    }
                }
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string sql = @"  select ROW_NUMBER() OVER() index,*  from (select t1.*,bdfwjzrydddzxcsl,czryrs,COALESCE(cdjksl,0) cdjksl,COALESCE(sqfwfsl,0) sqfwfsl,COALESCE(xy,0) xy,COALESCE(xd,0) xd,COALESCE(dy,0) dy from (SELECT DISTINCT zm_buildings.xzqdm,
                    zm_buildings.xzq 
                   FROM zm_buildings) t1 left join				 			 			 
                (select COALESCE(sum(qcxycdjk),0)+COALESCE(sum(tzzfybjcdssjk),0)  cdjksl,
                count(case when sfsqfwftcf=1 then 1 end) sqfwfsl,xzqdm,
                count(case when cddf=3 then 1 end)+count(case when bjcdssdfqk=3 then 1 end) xy,
                count(case when cddf=2 then 1 end)+count(case when bjcdssdfqk=2 then 1 end) xd,
                count(case when cddf=1 then 1 end)+count(case when bjcdssdfqk=1 then 1 end) dy
                from zm_pc_gjzz_cj WHERE   create_on between '" + startTime + " 00:00:00'and '" + endTime + " 23:59:59'   GROUP BY xzqdm) t2 on t1.xzqdm=t2.xzqdm     left join  (select  sum(bdfwjzrydddzxcsl) AS bdfwjzrydddzxcsl,sum(czryrs) czryrs,xzq, xzqdm     from (select m1.*   from (select t1.* from zm_pc_base t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) m1 ) t  where fwbh not in (select fwbh from "+ table_fwzt + " where final_create_on='" + endTime + " 23:59:59'   and final_fwzt is not null)  GROUP BY xzqdm, xzq) t3 on t1.xzqdm=t3.xzqdm ORDER BY t3.xzqdm) t where 1=1"
                 + strWhere ;


                //string sql = @" select ROW_NUMBER() OVER() index,*  from (select t1.*,bdfwjzrydddzxcsl,czryrs,COALESCE(cdjksl,0) cdjksl,COALESCE(sqfwfsl,0) sqfwfsl,COALESCE(xy,0) xy,COALESCE(xd,0) xd,COALESCE(dy,0) dy from (SELECT DISTINCT zm_buildings.xzqdm,
                //    zm_buildings.xzq 
                //   FROM zm_buildings) t1 left join				 			 			 
                //(select COALESCE(sum(qcxycdjk),0)+COALESCE(sum(tzzfybjcdssjk),0)  cdjksl,
                //count(case when sfsqfwftcf=1 then 1 end) sqfwfsl,xzqdm,
                //count(case when cddf=3 then 1 end)+count(case when bjcdssdfqk=3 then 1 end) xy,
                //count(case when cddf=2 then 1 end)+count(case when bjcdssdfqk=2 then 1 end) xd,
                //count(case when cddf=1 then 1 end)+count(case when bjcdssdfqk=1 then 1 end) dy
                //from zm_pc_gjzz_cj WHERE  create_on between '" + startTime + " 00:00:00'and '" + endTime + " 23:59:59'   GROUP BY xzqdm) t2 on t1.xzqdm=t2.xzqdm    " +
                // " left join  (select  sum(bdfwjzrydddzxcsl) AS bdfwjzrydddzxcsl,sum(czryrs) czryrs,xzq, xzqdm    " +
                // " from (select m1.*,hcfwzt from (select t1.* from zm_pc_base t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) m1  left join (select fwbh,fwzt hcfwzt from zm_pc_base_sqjz where   yhly='4') m3 " +
                // " on m1.fwbh=m3.fwbh) t  where hcfwzt is null and fwzt is null  GROUP BY xzqdm, xzq) t3 on t1.xzqdm=t3.xzqdm ORDER BY t3.xzqdm) t where 1=1 "
                // + strWhere;

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("充电方式统计");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("现有电动自行车数量");
                row.CreateCell(3).SetCellValue("现有充电接口数量");
                row.CreateCell(4).SetCellValue("收取服务费（个村）");
                row.CreateCell(5).SetCellValue("小于0.513元");
                row.CreateCell(6).SetCellValue("等于0.513元");
                row.CreateCell(7).SetCellValue("大于0.513元");
                row.CreateCell(8).SetCellValue("从事外卖、闪送、代驾等职业的人数");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "bdfwjzrydddzxcsl")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "cdjksl")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "sqfwfsl")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "xy")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "xd")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "dy")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "czryrs")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "充电设施配比情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 导出房屋底账
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("export/hourse/hourseExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult HoursesExportExcel([FromQuery] string startTime, [FromQuery] string endTime)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else if (user.RoleType == 4) //区级
                    {
                        strWhere += " and  towndm='" + model.RegionCode + "'";
                    }
                }
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string sql = @"select ROW_NUMBER() OVER ()index , b1.* from (select t1.* from (select t1.xzq,t1.xzqdm,t1.towndm,t1.town,t1.villagedm,t1.village,
                case when t2.hzxm is null then t1.hzxm else t2.hzxm end hzxm,case when t2.address is null then t1.address else t2.address end address,
                t1.fwbh,t2.jzrks,t2.zzrs,t2.fjsl,t2.jzfjs,t2.cs,t2.jzxyssltsl,t2.jzscxyaqcksl,t2.ckfhwggptzlsl,t2.bdfwjzrydddzxcsl,t2.mhqsl,t2.fytsmz,t2.xggdjsbp,t2.jzsyjyplmhxts,t2.jzszlwxdlgybjqs,t2.cjzwqtjyytmsg,t2.cdfsqtbz,t2.jzscmj from zm_buildings t1 left join zm_pc_base t2 on t1.fwbh=t2.fwbh ) t1) 
                b1  where    fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59')   " + strWhere + " order by xzqdm,villagedm";


                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("房屋台账");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("居住人口数");
                row.CreateCell(8).SetCellValue("租住人口数");
                row.CreateCell(9).SetCellValue("房间数量");
                row.CreateCell(10).SetCellValue("出租房间数量");
                row.CreateCell(11).SetCellValue("层数");
                row.CreateCell(12).SetCellValue("疏散楼梯数量");
                row.CreateCell(13).SetCellValue("安全出口数量");
                row.CreateCell(14).SetCellValue("防护网数量");
                row.CreateCell(15).SetCellValue("电动自行车数量");
                row.CreateCell(16).SetCellValue("灭火器数量");
                row.CreateCell(17).SetCellValue("防烟逃生面罩");
                row.CreateCell(18).SetCellValue("悬挂固定警示标牌");
                row.CreateCell(19).SetCellValue("简易喷淋灭火系统数");
                row.CreateCell(20).SetCellValue("联网型独立感烟报警器数");
                row.CreateCell(21).SetCellValue("其他经营业态");
                row.CreateCell(22).SetCellValue("从事外卖、闪送、代驾等职业的人数");
                row.CreateCell(23).SetCellValue("房屋面积");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            //var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value+"");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzrks")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zzrs")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fjsl")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzfjs")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cs")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzxyssltsl")
                            {
                                row.CreateCell(12).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzscxyaqcksl")
                            {
                                row.CreateCell(13).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "ckfhwggptzlsl")
                            {
                                row.CreateCell(14).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "bdfwjzrydddzxcsl")
                            {
                                row.CreateCell(15).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "mhqsl")
                            {
                                row.CreateCell(16).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fytsmz")
                            {
                                row.CreateCell(17).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "xggdjsbp")
                            {
                                row.CreateCell(18).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzsyjyplmhxts")
                            {
                                row.CreateCell(19).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzszlwxdlgybjqs")
                            {
                                row.CreateCell(20).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cjzwqtjyytmsg")
                            {
                                row.CreateCell(22).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cdfsqtbz")
                            {
                                row.CreateCell(22).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzscmj")
                            {
                                row.CreateCell(23).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "房屋底账" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出抽检情况
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/task/taskExportExcel")]
        [Produces("application/octet-stream")]
        public IActionResult taskExportExcel([FromQuery] string taskid, [FromQuery] string dbzt, [FromQuery] string txtKeyWord)
        {
            try
            {

                var userNumber = this.CurrentUser();
               
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

                string strWhere = "";
                if (!string.IsNullOrEmpty(dbzt))
                {
                    if (dbzt == "1")
                    {
                        strWhere += " and REPLACE(db_pencent, '%', '')::numeric >= 98.37";
                    }
                    else if (dbzt == "2")
                    {
                        strWhere += " and REPLACE(db_pencent, '%', '')::numeric < 98.37";
                    }
                }
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (villagedm like '%" + txtKeyWord + "%' or village like '%" + txtKeyWord + "%'   or towndm like '%" + txtKeyWord + "%' or town like '%" + txtKeyWord + "%')";
                }

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;

                //查询上本次任务抽取时间
                string sql2 = "select create_on from zm_task where id='" + taskid + "'";
                var list2 = _dbContext.Database.GetDbConnection().Query<object>(sql2).ToList();
                string create_on = "";
                foreach (object obj in list2)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {
                        var properties = obj as IDictionary<string, object>;
                        foreach (var kvp in properties)
                        {
                            if (kvp.Key == "create_on")
                            {
                                create_on = kvp.Value.ToString();
                            }
                        }
                    }
                }


                string year = DateTime.Now.Year.ToString();

                string sql = @"SELECT ROW_NUMBER( ) OVER ( ) INDEX,* FROM (select  t3.region_name xzq,t4.region_name town,t5.region_name village,t1.*,	t6.zccount,
		COALESCE(t6.twocount,0) twocount,COALESCE(t6.onecount,0)  onecount,COALESCE(t7.sycount,0) sycount,COALESCE(t7.twosycount,0) twosycount,COALESCE(t7.onesycount,0) onesycount ,COALESCE(t2.ydb,0) ydbcount,COALESCE(t2.wdb,0) wdbcount,
                           CASE WHEN hycount = 0 THEN '0.00%'   ELSE concat(CASE WHEN (round(((COALESCE(t2.ydb,0))::NUMERIC/(hycount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((COALESCE(t2.ydb,0))::NUMERIC/(hycount)::NUMERIC)*(100)::NUMERIC, 2)  END,'%') END db_pencent,
			               (SELECT string_agg(DISTINCT(u.nickname), '、') FROM zm_task_zuser u  WHERE u.number = ANY(string_to_array(t1.jcr, '、'))) AS jcr_nicknames
                           from (SELECT DISTINCT(villagedm) villagedm,towndm,xzqdm,COUNT (fwbh)  cqcount,SUM ( CASE WHEN cs > 1 THEN 1 ELSE 0 END ) twocqcount,
	SUM ( CASE WHEN cs = 1 THEN 1 ELSE 0 END ) onecqcount,sum(case when hyzt='1' then 1 else 0 end ) hycount,sum(case when (hyzt is null) then 1 else 0 end) whycount,string_agg(distinct(number),'、') jcr FROM zm_taskdetail  WHERE taskid = '" + taskid + "'  GROUP BY villagedm,towndm,xzqdm) t1 " +
                               "left join (SELECT  xzqdm,towndm,villagedm,COUNT ( fwbh ) zccount,SUM ( CASE WHEN cs >= 2 THEN 1 ELSE 0 END ) twocount,SUM ( CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END ) onecount FROM  zm_buildings WHERE fwbh NOT IN ( SELECT DISTINCT fwbh FROM " + table_fwzt + " WHERE ( final_fwzt IS NOT NULL ) AND ( final_create_on = '" + today + " 23:59:59' ) ) GROUP BY  xzqdm,towndm,villagedm  ) t6 ON t1.villagedm = t6.villagedm" +
                               "  left join (SELECT xzqdm, towndm, villagedm,count(fwbh) sycount, SUM(CASE WHEN cs >= 2 THEN 1 ELSE 0 END) twosycount, SUM(CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END) onesycount FROM zm_buildings  WHERE  fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on =  '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='"+ endData.Year + "' and taskid in (select id from zm_task where create_on < '" + create_on + "' )    )  GROUP BY xzqdm, towndm, villagedm) t7 ON t1.villagedm = t7.villagedm" +
                               "  left join (select village villagedm,COALESCE(sum(case when jsrysstd='是' then 1 else 0 END),0) ydb,COALESCE(sum(case when jsrysstd='否' then 1 else 0 END),0) wdb from zm_pc_baselog_sqjz  WHERE   id  in (select baseid from zm_taskdetail WHERE taskid = '" + taskid + "' )  group by village,town,xzqdm) t2 on t1.villagedm=t2.villagedm left join zm_department t3 on t1.xzqdm=(t3.id)  left join zm_department t4 on concat(t1.towndm,'000')=(t4.id) left join zm_department t5 on concat(t1.villagedm)=(t5.id) )  tt where 1=1 " + strWhere;
                
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("抽检任务-村庄明细");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("行政区");
                row.CreateCell(2).SetCellValue("乡镇名称");
                row.CreateCell(3).SetCellValue("村名称");
                row.CreateCell(4).SetCellValue("房屋总量");
                row.CreateCell(5).SetCellValue("二层及以上总量");
                row.CreateCell(6).SetCellValue("一层总量");
                row.CreateCell(7).SetCellValue("上次抽检剩余量");
                row.CreateCell(8).SetCellValue("二层及以上剩余量");
                row.CreateCell(9).SetCellValue("一层剩余量");
                row.CreateCell(10).SetCellValue("本次抽检量");
                row.CreateCell(11).SetCellValue("二层及以上抽检量");
                row.CreateCell(12).SetCellValue("一层抽检量");
                row.CreateCell(13).SetCellValue("检查人");
                row.CreateCell(14).SetCellValue("已核验数量");
                row.CreateCell(15).SetCellValue("未核验数量");
                row.CreateCell(16).SetCellValue("已达标数量");
                row.CreateCell(17).SetCellValue("未达标数量");
                row.CreateCell(18).SetCellValue("达标率");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "zccount")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "twocount")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "onecount")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "sycount")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "twosycount")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            else if (kvp.Key == "onesycount")
                            {
                                row.CreateCell(9).SetCellValue(val);
                            }
                            else if (kvp.Key == "cqcount")
                            {
                                row.CreateCell(10).SetCellValue(val);
                            }
                            else if (kvp.Key == "twocqcount")
                            {
                                row.CreateCell(11).SetCellValue(val);
                            }
                            else if (kvp.Key == "onecqcount")
                            {
                                row.CreateCell(12).SetCellValue(val);
                            }
                            else if (kvp.Key == "jcr_nicknames")
                            {
                                row.CreateCell(13).SetCellValue(val);
                            }
                            else if (kvp.Key == "hycount")
                            {
                                row.CreateCell(14).SetCellValue(val);
                            }
                            else if (kvp.Key == "whycount")
                            {
                                row.CreateCell(15).SetCellValue(val);
                            }
                            else if (kvp.Key == "ydbcount")
                            {
                                row.CreateCell(16).SetCellValue(val);
                            }
                            else if (kvp.Key == "wdbcount")
                            {
                                row.CreateCell(17).SetCellValue(val);
                            }
                            else if (kvp.Key == "db_pencent")
                            {
                                row.CreateCell(18).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "抽检任务-村庄明细" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }




        /// <summary>
        /// 导出隐患信息
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("export/static/yhExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult GetYhExport([FromQuery] string startTime, [FromQuery] string endTime,[FromQuery] string yhly, [FromQuery] string levle, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else  //乡镇
                    {
                        strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                    }
                }

                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                string sql = "";
                string toDay = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(toDay);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                }
                string role_type = "";
                if (!string.IsNullOrEmpty(yhly))
                {
                    role_type = yhly;
                }
                
                if (levle == "xzq")
                {
                    sql = @$"select ROW_NUMBER() OVER ()index ,* from (select sum(sfczckfhwggptzl)sfczckfhwggptzl,
	                                sum(slljdx)slljdx,
	                                sum(ygbjwfwhyx)ygbjwfwhyx,
	                                sum(use_stacked_items)use_stacked_items,
	                                sum(mhqwbcwhyx)mhqwbcwhyx,
	                                sum(use_electric_bicycle)use_electric_bicycle,
	                                sum(use_bottled_liquefied_gas)use_bottled_liquefied_gas,
	                                sum(jyplmhxtwbcwhyx)jyplmhxtwbcwhyx,
	                                sum(sfczckfhwggptzl_sycount)sfczckfhwggptzl_sycount,
	                                sum(slljdx_sycount)slljdx_sycount,
	                                sum(ygbjwfwhyx_sycount)ygbjwfwhyx_sycount,
	                                sum(use_stacked_items_sycount)use_stacked_items_sycount,
	                                sum(mhqwbcwhyx_sycount)mhqwbcwhyx_sycount,
	                                sum(use_electric_bicycle_sycount)use_electric_bicycle_sycount,
	                                sum(use_bottled_liquefied_gas_sycount)use_bottled_liquefied_gas_sycount,
	                                sum(jyplmhxtwbcwhyx_sycount)jyplmhxtwbcwhyx_sycount,
	                                sum(sfczckfhwggptzl_zgcount)sfczckfhwggptzl_zgcount,
	                                sum(slljdx_zgcount)slljdx_zgcount,
	                                sum(ygbjwfwhyx_zgcount)ygbjwfwhyx_zgcount,
	                                sum(use_stacked_items_zgcount)use_stacked_items_zgcount,
	                                sum(mhqwbcwhyx_zgcount)mhqwbcwhyx_zgcount,
	                                sum(use_electric_bicycle_zgcount)use_electric_bicycle_zgcount,
	                                sum(use_bottled_liquefied_gas_zgcount)use_bottled_liquefied_gas_zgcount,
	                                sum(jyplmhxtwbcwhyx_zgcount)jyplmhxtwbcwhyx_zgcount,
	                                sum(yhtotal) yhtotal,
	                                sum(zgtotal) zgtotal,
	                                sum(sytotal) sytotal,xzq,xzqdm from(" +
                               " select* from pro_yh_list('" + startTime + "','" + endTime + " 23:59:59','','zm_pc_yhlog_dx_sqjz','zm_pc_yhlog_dxzgson_sqjz','" + table_fwzt + "')" +
                               " union all" +
                               " select * from pro_yh_list('" + startTime + "','" + endTime + " 23:59:59','','zm_pc_yhlog_dx_cj','zm_pc_yhlog_dxzgson_cj','" + table_fwzt + "'))t group by xzq,xzqdm order by xzqdm) t where 1=1 " + strWhere;
                    ;
                }
                else if (levle == "town") 
                {
                    sql = @"  select ROW_NUMBER() OVER ()index ,* from (select sum(sfczckfhwggptzl)sfczckfhwggptzl,
                                    sum(slljdx)slljdx,
	                                sum(ygbjwfwhyx)ygbjwfwhyx,
	                                sum(use_stacked_items)use_stacked_items,
	                                sum(mhqwbcwhyx)mhqwbcwhyx,
	                                sum(use_electric_bicycle)use_electric_bicycle,
	                                sum(use_bottled_liquefied_gas)use_bottled_liquefied_gas,
	                                sum(jyplmhxtwbcwhyx)jyplmhxtwbcwhyx,
	                                sum(sfczckfhwggptzl_sycount)sfczckfhwggptzl_sycount,
	                                sum(slljdx_sycount)slljdx_sycount,
	                                sum(ygbjwfwhyx_sycount)ygbjwfwhyx_sycount,
	                                sum(use_stacked_items_sycount)use_stacked_items_sycount,
	                                sum(mhqwbcwhyx_sycount)mhqwbcwhyx_sycount,
	                                sum(use_electric_bicycle_sycount)use_electric_bicycle_sycount,
	                                sum(use_bottled_liquefied_gas_sycount)use_bottled_liquefied_gas_sycount,
	                                sum(jyplmhxtwbcwhyx_sycount)jyplmhxtwbcwhyx_sycount,
	                                sum(sfczckfhwggptzl_zgcount)sfczckfhwggptzl_zgcount,
	                                sum(slljdx_zgcount)slljdx_zgcount,
	                                sum(ygbjwfwhyx_zgcount)ygbjwfwhyx_zgcount,
	                                sum(use_stacked_items_zgcount)use_stacked_items_zgcount,
	                                sum(mhqwbcwhyx_zgcount)mhqwbcwhyx_zgcount,
	                                sum(use_electric_bicycle_zgcount)use_electric_bicycle_zgcount,
	                                sum(use_bottled_liquefied_gas_zgcount)use_bottled_liquefied_gas_zgcount,
	                                sum(jyplmhxtwbcwhyx_zgcount)jyplmhxtwbcwhyx_zgcount,
	                                sum(yhtotal) yhtotal,
	                                sum(zgtotal) zgtotal,
	                                sum(sytotal) sytotal,xzq,xzqdm,town,towndm from(" +
                                " select* from pro_yh_xz_list('" + startTime + "','" + endTime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_sqjz','zm_pc_yhlog_dxzgson_sqjz','" + xzqdm + "','" + table_fwzt + "')" +
                                " union all" +
                                " select * from pro_yh_xz_list('" + startTime + "','" + endTime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_cj','zm_pc_yhlog_dxzgson_cj','" + xzqdm + "','" + table_fwzt + "'))t" +
                                " group by xzq,xzqdm,town,towndm order by towndm) t where 1=1 " + strWhere;

                }
                else if (levle == "village")
                {
                    sql = @"  select ROW_NUMBER() OVER ()index ,* from (select sum(sfczckfhwggptzl)sfczckfhwggptzl,
                                    sum(slljdx)slljdx,
	                                sum(ygbjwfwhyx)ygbjwfwhyx,
	                                sum(use_stacked_items)use_stacked_items,
	                                sum(mhqwbcwhyx)mhqwbcwhyx,
	                                sum(use_electric_bicycle)use_electric_bicycle,
	                                sum(use_bottled_liquefied_gas)use_bottled_liquefied_gas,
	                                sum(jyplmhxtwbcwhyx)jyplmhxtwbcwhyx,
	                                sum(sfczckfhwggptzl_sycount)sfczckfhwggptzl_sycount,
	                                sum(slljdx_sycount)slljdx_sycount,
	                                sum(ygbjwfwhyx_sycount)ygbjwfwhyx_sycount,
	                                sum(use_stacked_items_sycount)use_stacked_items_sycount,
	                                sum(mhqwbcwhyx_sycount)mhqwbcwhyx_sycount,
	                                sum(use_electric_bicycle_sycount)use_electric_bicycle_sycount,
	                                sum(use_bottled_liquefied_gas_sycount)use_bottled_liquefied_gas_sycount,
	                                sum(jyplmhxtwbcwhyx_sycount)jyplmhxtwbcwhyx_sycount,
	                                sum(sfczckfhwggptzl_zgcount)sfczckfhwggptzl_zgcount,
	                                sum(slljdx_zgcount)slljdx_zgcount,
	                                sum(ygbjwfwhyx_zgcount)ygbjwfwhyx_zgcount,
	                                sum(use_stacked_items_zgcount)use_stacked_items_zgcount,
	                                sum(mhqwbcwhyx_zgcount)mhqwbcwhyx_zgcount,
	                                sum(use_electric_bicycle_zgcount)use_electric_bicycle_zgcount,
	                                sum(use_bottled_liquefied_gas_zgcount)use_bottled_liquefied_gas_zgcount,
	                                sum(jyplmhxtwbcwhyx_zgcount)jyplmhxtwbcwhyx_zgcount,
	                                sum(yhtotal) yhtotal,
	                                sum(zgtotal) zgtotal,
	                                sum(sytotal) sytotal,xzq,xzqdm,town,towndm,villagedm,village from(" +
                                " select* from pro_yh_cj_list('" + startTime + "','" + endTime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_sqjz','zm_pc_yhlog_dxzgson_sqjz','" + xzqdm + "','" + towndm + "','" + table_fwzt + "')" +
                                " union all" +
                                " select * from pro_yh_cj_list('" + startTime + "','" + endTime + " 23:59:59','" + role_type + "','zm_pc_yhlog_dx_cj','zm_pc_yhlog_dxzgson_cj','" + xzqdm + "','" + towndm + "','" + table_fwzt + "'))t" +
                                " group by xzq,xzqdm,town,towndm,villagedm,village order by villagedm) t where 1=1 " + strWhere;
                    ;
                }

               
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("隐患信息");

                ICellStyle mainHeaderStyle = wb.CreateCellStyle();
                mainHeaderStyle.Alignment = HorizontalAlignment.Center;
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("名称");
                row.CreateCell(2).SetCellValue("汇总");
                row.CreateCell(3).SetCellValue("");
                row.CreateCell(4).SetCellValue("");
                row.CreateCell(5).SetCellValue("是否存在窗口防护网、广告牌、铁栅栏");
                row.CreateCell(6).SetCellValue("");
                row.CreateCell(7).SetCellValue("");
                row.CreateCell(8).SetCellValue("电动自行车、电动平衡车及其蓄电池在室内及安全出口处停放、充电");
                row.CreateCell(9).SetCellValue("");
                row.CreateCell(10).SetCellValue("");
                row.CreateCell(11).SetCellValue("在公共区域、租住房间内、地下室使用瓶装液化石油气");
                row.CreateCell(12).SetCellValue("");
                row.CreateCell(13).SetCellValue("");
                row.CreateCell(14).SetCellValue("疏散走道、楼梯间、安全出口处堆放物品影响人员疏散");
                row.CreateCell(15).SetCellValue("");
                row.CreateCell(16).SetCellValue("");
                row.CreateCell(17).SetCellValue("私拉乱接电线");
                row.CreateCell(18).SetCellValue("");
                row.CreateCell(19).SetCellValue("");
                row.CreateCell(20).SetCellValue("灭火器未保持完好有效");
                row.CreateCell(21).SetCellValue("");
                row.CreateCell(22).SetCellValue("");
                row.CreateCell(23).SetCellValue("简易喷淋灭火系统未保持完好有效");
                row.CreateCell(24).SetCellValue("");
                row.CreateCell(25).SetCellValue("");
                row.CreateCell(26).SetCellValue("烟感报警器未保持完好有效");
                row.CreateCell(27).SetCellValue("");
                row.CreateCell(28).SetCellValue("");



                // 合并单元格创建多级标题
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 2, 4)); // 合并
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 5, 7)); // 合并
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 8, 10)); // 合并
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 11, 13)); // 合并
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 14, 16)); // 合并
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 17, 19)); // 合并
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 20, 22)); // 合并
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 23, 25)); // 合并
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 26, 28)); // 合并
                IRow row1 = sheet.CreateRow(1);
                row1.CreateCell(0).SetCellValue("");
                row1.CreateCell(1).SetCellValue("");
                row1.CreateCell(2).SetCellValue("隐患总数");
                row1.CreateCell(3).SetCellValue("整改总数");
                row1.CreateCell(4).SetCellValue("剩余总数");
                row1.CreateCell(5).SetCellValue("隐患数量");
                row1.CreateCell(6).SetCellValue("整改数量");
                row1.CreateCell(7).SetCellValue("剩余数量");
                row1.CreateCell(8).SetCellValue("隐患数量");
                row1.CreateCell(9).SetCellValue("整改数量");
                row1.CreateCell(10).SetCellValue("剩余数量");
                row1.CreateCell(11).SetCellValue("隐患数量");
                row1.CreateCell(12).SetCellValue("整改数量");
                row1.CreateCell(13).SetCellValue("剩余数量");
                row1.CreateCell(14).SetCellValue("隐患数量");
                row1.CreateCell(15).SetCellValue("整改数量");
                row1.CreateCell(16).SetCellValue("剩余数量");
                row1.CreateCell(17).SetCellValue("隐患数量");
                row1.CreateCell(18).SetCellValue("整改数量");
                row1.CreateCell(19).SetCellValue("剩余数量");
                row1.CreateCell(20).SetCellValue("隐患数量");
                row1.CreateCell(21).SetCellValue("整改数量");
                row1.CreateCell(22).SetCellValue("剩余数量");
                row1.CreateCell(23).SetCellValue("隐患数量");
                row1.CreateCell(24).SetCellValue("整改数量");
                row1.CreateCell(25).SetCellValue("剩余数量");
                row1.CreateCell(26).SetCellValue("隐患数量");
                row1.CreateCell(27).SetCellValue("整改数量");
                row1.CreateCell(28).SetCellValue("剩余数量");

                // 第二行 写数据
                int i = 2;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            //var val = kvp.Value+"";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i-1);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "yhtotal")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zgtotal")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "sytotal")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "sfczckfhwggptzl")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "sfczckfhwggptzl_zgcount")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "sfczckfhwggptzl_sycount")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_electric_bicycle")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_electric_bicycle_zgcount")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_electric_bicycle_sycount")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_bottled_liquefied_gas")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_bottled_liquefied_gas_zgcount")
                            {
                                row.CreateCell(12).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_bottled_liquefied_gas_sycount")
                            {
                                row.CreateCell(13).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_stacked_items")
                            {
                                row.CreateCell(14).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_stacked_items_zgcount")
                            {
                                row.CreateCell(15).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_stacked_items_sycount")
                            {
                                row.CreateCell(16).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "slljdx")
                            {
                                row.CreateCell(17).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "slljdx_zgcount")
                            {
                                row.CreateCell(18).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "slljdx_sycount")
                            {
                                row.CreateCell(19).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "mhqwbcwhyx")
                            {
                                row.CreateCell(20).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "mhqwbcwhyx_zgcount")
                            {
                                row.CreateCell(21).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "mhqwbcwhyx_sycount")
                            {
                                row.CreateCell(22).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jyplmhxtwbcwhyx")
                            {
                                row.CreateCell(23).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jyplmhxtwbcwhyx_zgcount")
                            {
                                row.CreateCell(24).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jyplmhxtwbcwhyx_sycount")
                            {
                                row.CreateCell(25).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "ygbjwfwhyx")
                            {
                                row.CreateCell(26).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "ygbjwfwhyx_zgcount")
                            {
                                row.CreateCell(27).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "ygbjwfwhyx_sycount")
                            {
                                row.CreateCell(28).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "隐患信息统计" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }










        /// <summary>
        /// 导出隐患明细
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("export/static/GetHyYhMxExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult GetHyYhMxExport([FromQuery] string check_id, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord, [FromQuery] string yhly, [FromQuery] string startime, [FromQuery] string endtime, [FromQuery] string zgvalue, [FromQuery] string type)
        {
            try
            {
                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
                if (model != null)
                {
                     if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                }
                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and (towndm='" + towndm + "' or town='" + towndm + "')";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and (villagedm='" + villagedm + "' or village='" + villagedm + "')";
                }
                if (string.IsNullOrEmpty(startime) && string.IsNullOrEmpty(endtime))
                {
                    startime = "" + startime + "";
                    endtime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (type == "zg")
                {
                    strWhere += " and zgvalue='已整改'";
                }
                else if (type == "sy")
                {
                    strWhere += " and zgvalue='未整改'";
                }
                else if (type == "yh")
                {
                    strWhere += " and create_on BETWEEN '" + startime + "' and '" + endtime + " 23:59:59' ";
                }
                if (check_id != "sytotal" && check_id != "zgtotal" && check_id != "yhtotal")
                {
                    strWhere += " and check_id='" + check_id + "'";
                }
                
                if (!string.IsNullOrEmpty(zgvalue))
                {
                    strWhere += " and zgvalue='" + zgvalue + "'";
                }
                string toDay = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endtime))
                {
                    DateTime endData = Convert.ToDateTime(endtime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                    if (Convert.ToDateTime(endtime) > DateTime.Now)
                    {
                        table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                    }
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(toDay);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                if (!string.IsNullOrEmpty(yhly))
                {
                    strWhere += " and jcly ='" + yhly + "'";
                }
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' OR address like '%" + txtKeyWord + "%' OR fwbh like '%" + txtKeyWord + "%' ) ";
                }

                string sql = @$"  select ROW_NUMBER() OVER()index, *from(
         select  xzq, xzqdm, towndm town, town towndm, villagedm village, village villagedm, hzxm, address, fwbh, check_id, check_name, create_on,case when jcly= 3 then '区级核验' when jcly = 4 then
        '乡镇核验' when jcly = 2 then '市级核验' end yhly, create_name, zgvalue from(select distinct * from(select t1.id, t1.xzqdm, t1.xzq, t1.town towndm, t1.towndm town, t1.village
        villagedm, t1.villagedm  village, t2.hzxm, t2.address, t1.fwbh, t1.zg_value, t1.create_on, t1.check_id, t1.check_name, t1.jcly, t1.check_nickname create_name,case when zg_value= 1 and t1.id in (select yhlog_dx_id from zm_pc_yhlog_dxzgson_sqjz where zg_time BETWEEN '2024-01-01' and '"+ endtime + " 23:59:59')  then  '已整改' else '未整改' end zgvalue  from zm_pc_yhlog_dx_sqjz t1   left join zm_buildings t2 on t1.fwbh = t2.fwbh) t2) t where fwbh not in(select distinct fwbh from " + table_fwzt +
      "  where final_fwzt is not null and final_create_on='" + endtime + " 23:59:59')  " + strWhere +
      "       union all     " +
      "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue from " +
      "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
      "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=1 and t1.id in (select yhlog_dx_id from zm_pc_yhlog_dxzgson_cj where zg_time  BETWEEN '2024-01-01' and '"+ endtime + " 23:59:59')  then  '已整改' else '未整改' end zgvalue from zm_pc_yhlog_dx_cj t1 " +
      " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings ) t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
      "  final_create_on='" + endtime + " 23:59:59')   " + strWhere +
      "   ) t where 1=1      ";

                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("隐患信息");

                ICellStyle mainHeaderStyle = wb.CreateCellStyle();
                mainHeaderStyle.Alignment = HorizontalAlignment.Center;
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇");
                row.CreateCell(3).SetCellValue("村名");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("隐患项名称");
                
                row.CreateCell(8).SetCellValue("是否整改");
                row.CreateCell(9).SetCellValue("隐患来源");
                row.CreateCell(10).SetCellValue("检查时间");
                row.CreateCell(11).SetCellValue("检查人员");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            //var val = kvp.Value+"";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "check_name")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zgvalue")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "yhly")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "create_on")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "create_name")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "隐患信息明细" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 导出基本信息
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("export/static/HourseJbxxExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult HourseJbxxExport([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string category,[FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "  and 1=1";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else  //乡镇
                    {
                        strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                    }
                }

                string sheetName = " ";
                string xzqWhere = "";
                string where = "";
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "'";
                    xzqWhere = " and t1.xzqdm='" + xzqdm + "' and  t1.town='" + towndm + "' and  t1.village='" + villagedm + "'";
                    where = " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' and villagedm='" + villagedm + "'";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' ";
                    xzqWhere = " and t1.xzqdm='" + xzqdm + "' and t1.town='" + towndm + "' ";
                    where = " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' ";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                    xzqWhere = " and t1.xzqdm='" + xzqdm + "' ";
                    where = " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    strWhere += " and (create_on BETWEEN '" + startTime + " 00:00:00' AND '" + endTime + " 23:59:59' )";
                }

                string sql = " ";

                if (category == "1")
                {
                    sql = @" 	select ROW_NUMBER() OVER ()index,* from (		SELECT xzq,xzqdm,villagedm,towndm,town,    address,hzxm, create_on,village, cs, 
             jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM (     select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,
             address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,
          t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt,tdsfdb  from zm_pc_base t1
         JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh "
         + "WHERE  1=1 "+ xzqWhere + 
            " order by t1.village) AS t  where row_num =1   and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)  and hcfwzt is null  and  ( tdsfdb='否')  "
       +" and 1=1 "+strWhere+" order by village) t  where 1=1  ) AS t "
            + "    UNION "
            + "    select  xzq,xzqdm,  villagedm,  towndm, town,"
            + "    address,hzxm, \"CreateOn\" create_on, village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh from (select t1.*,fwzt from (select xzq,xzqdm, village villagedm, town towndm,towndm town,"
            + "    address,hzxm, \"CreateOn\",villagedm village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh  from zm_buildings where fwbh not in(select fwbh from zm_pc_yh "
            + "   where 1=1 " + where + ")     " + where + ")t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) t "
            + "  where fwzt is null order by village ) t  where 1=1  "; 
                    sheetName = "不满足双通道情况";
                }
                else if (category == "2")
                {
                    sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
                + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
                + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
                + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,sfyzc,fwzt  from zm_pc_baselog t1"
                + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh "
                + "    WHERE  sfyzc='是'  " + xzqWhere + "  and t1.create_on BETWEEN '" + startTime + " 00:00:00 ' and '" + endTime + " 23:59:59'  "
                + "     order by t1.village) AS t  where row_num =1  and sfyzc='是' and fwzt IS NULL  and   ( sstdjsqk='正在施工改造')  "
                + "    and 1=1 " + strWhere
                + "    UNION "
                + "    select  xzq,xzqdm,  villagedm,  towndm, town,"
                + "    address,hzxm, \"CreateOn\" create_on, village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh from (select t1.*,fwzt from (select xzq,xzqdm, village villagedm, town towndm,towndm town,"
                + "    address,hzxm, \"CreateOn\",villagedm village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh  from zm_buildings where fwbh not in(select fwbh from zm_pc_yh "
                + "   where 1=1 " + where + ")     " + where + ")t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) t "
                + "  where fwzt is null) t  where 1=1 order by village  ";
                    sheetName = "正在施工改造情况";
                }
                else if (category == "3")
                {
                    sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
           + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
           + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
           + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,sfyzc,fwzt  from zm_pc_baselog t1"
           + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh "
           + "    WHERE  sfyzc='是'  " + xzqWhere + "  and t1.create_on BETWEEN '" + startTime + " 00:00:00 ' and '" + endTime + " 23:59:59'  "
           + "     order by t1.village) AS t  where row_num =1  and sfyzc='是' and fwzt IS NULL  and   ( sstdjsqk='正在制定方案')  "
           + "    and 1=1 " + strWhere
           + "    UNION "
           + "    select  xzq,xzqdm,  villagedm,  towndm, town,"
           + "    address,hzxm, \"CreateOn\" create_on, village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh from (select t1.*,fwzt from (select xzq,xzqdm, village villagedm, town towndm,towndm town,"
           + "    address,hzxm, \"CreateOn\",villagedm village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh  from zm_buildings where fwbh not in(select fwbh from zm_pc_yh "
           + "   where 1=1 " + where + ")     " + where + ")t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) t "
           + "  where fwzt is null) t  where 1=1  order by village";
                    sheetName = "正在制定方案情况";
                }
                else if (category == "4")
                {
                    sql = @"  select ROW_NUMBER() OVER()index,*from(SELECT xzq, xzqdm, villagedm, towndm, town, "
                + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
                + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
                + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,sfyzc,fwzt  from zm_pc_baselog t1"
                + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh "
                + "    WHERE  sfyzc='是'  " + xzqWhere + "  and t1.create_on BETWEEN '" + startTime + " 00:00:00 ' and '" + endTime + " 23:59:59'  "
                + "     order by t1.village) AS t  where row_num =1  and sfyzc='是' and fwzt IS NULL  and  ((xggdjsbp is null OR xggdjsbp=0))  "
                + "    and 1=1 " + strWhere
                + "    UNION "
                + "    select  xzq,xzqdm,  villagedm,  towndm, town,"
                + "    address,hzxm, \"CreateOn\" create_on, village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh from (select t1.*,fwzt from (select xzq,xzqdm, village villagedm, town towndm,towndm town,"
                + "    address,hzxm, \"CreateOn\",villagedm village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh  from zm_buildings where fwbh not in(select fwbh from zm_pc_yh "
                + "   where 1=1 " + where + ")     " + where + ")t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) t "
                + "  where fwzt is null) t  where 1=1 order by village ";
                    sheetName = "悬挂固定警示标牌情况";
                }
                else if (category == "5")
                {
                    sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
      + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
      + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
      + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,sfyzc,fwzt  from zm_pc_baselog t1"
      + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh "
      + "    WHERE  sfyzc='是'  " + xzqWhere + "  and t1.create_on BETWEEN '" + startTime + " 00:00:00 ' and '" + endTime + " 23:59:59'  "
      + "     order by t1.village) AS t  where row_num =1  and sfyzc='是' and fwzt IS NULL  and  (cs=2)  "
      + "    and 1=1 " + strWhere
      + "    UNION "
      + "    select  xzq,xzqdm,  villagedm,  towndm, town,"
      + "    address,hzxm, \"CreateOn\" create_on, village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh from (select t1.*,fwzt from (select xzq,xzqdm, village villagedm, town towndm,towndm town,"
      + "    address,hzxm, \"CreateOn\",villagedm village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh  from zm_buildings where fwbh not in(select fwbh from zm_pc_yh "
      + "   where 1=1 " + where + ")     " + where + ")t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) t "
      + "  where fwzt is null) t  where 1=1 order by village ";
                    sheetName = "两层房屋基本情况";
                }
                else if (category == "6")
                {
                    sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
          + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
          + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
          + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,sfyzc,fwzt  from zm_pc_baselog t1"
          + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh "
          + "    WHERE  sfyzc='是'  " + xzqWhere + "  and t1.create_on BETWEEN '" + startTime + " 00:00:00 ' and '" + endTime + " 23:59:59'  "
          + "     order by t1.village) AS t  where row_num =1  and sfyzc='是' and fwzt IS NULL  and  (cs>=3)  "
          + "    and 1=1 " + strWhere
          + "    UNION "
          + "    select  xzq,xzqdm,  villagedm,  towndm, town,"
          + "    address,hzxm, \"CreateOn\" create_on, village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh from (select t1.*,fwzt from (select xzq,xzqdm, village villagedm, town towndm,towndm town,"
          + "    address,hzxm, \"CreateOn\",villagedm village, cs, '0' jzscxyaqcksl,'0' jzxyssltsl,'' sstdjsqk,'0'  xggdjsbp, fwbh  from zm_buildings where fwbh not in(select fwbh from zm_pc_yh "
          + "   where 1=1 " + where + ")     " + where + ")t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) t "
          + "  where fwzt is null) t  where 1=1 order by village ";

                    sheetName = "三层及三层以上房屋基本情况";
                }
                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("房屋基本信息");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("层数");
                row.CreateCell(8).SetCellValue("现有安全出口数量");
                row.CreateCell(9).SetCellValue("现有疏散楼梯数量");
                row.CreateCell(10).SetCellValue("建设疏散楼梯情况");
                row.CreateCell(11).SetCellValue("悬挂警示标牌数量");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            //var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "towndm")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cs")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzscxyaqcksl")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jzxyssltsl")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "sstdjsqk")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "xggdjsbp")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }

                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "基本信息（"+ sheetName + "）" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出临时
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/other/builldings")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult builldings([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string txtKeyWord, [FromQuery] string yhly, [FromQuery] string cked, [FromQuery] string jsrysstd, [FromQuery] string wdwhjgfjgqsyyrkrgbdj, [FromQuery] string sfczckfhwggptzl, [FromQuery] string use_electric_bicycle, [FromQuery] string fwyfwdddzxccdsssfayqjshsf, [FromQuery] string use_bottled_liquefied_gas, [FromQuery] string use_stacked_items, [FromQuery] string slljdx, [FromQuery] string cs, [FromQuery] string nickname)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = DateTime.Now.ToShortDateString();
                    endTime = DateTime.Now.ToShortDateString();
                }
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (fwbh='" + txtKeyWord + "' or hzxm like '%" + txtKeyWord + "%' OR address like '%" + txtKeyWord + "%')";
                }
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "'";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' ";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                }
                if (cked == "true")
                {
                    strWhere += " and village in (select villagedm from zdcmd )  ";
                }
                if (!string.IsNullOrEmpty(nickname))
                {
                    strWhere += " and create_name ='" + nickname + "'  ";
                }
                if (!string.IsNullOrEmpty(slljdx))
                {
                    strWhere += " and slljdx ='" + slljdx + "'  ";
                }
                if (!string.IsNullOrEmpty(use_stacked_items))
                {
                    strWhere += " and use_stacked_items ='" + use_stacked_items + "'  ";
                }
                if (!string.IsNullOrEmpty(use_bottled_liquefied_gas))
                {
                    strWhere += " and use_bottled_liquefied_gas ='" + use_bottled_liquefied_gas + "'  ";
                }
                if (!string.IsNullOrEmpty(fwyfwdddzxccdsssfayqjshsf))
                {
                    strWhere += " and fwyfwdddzxccdsssfayqjshsf ='" + fwyfwdddzxccdsssfayqjshsf + "' ";
                }
                if (!string.IsNullOrEmpty(use_electric_bicycle))
                {
                    strWhere += " and use_electric_bicycle ='" + use_electric_bicycle + "' ";
                }
                if (!string.IsNullOrEmpty(sfczckfhwggptzl))
                {
                    strWhere += " and sfczckfhwggptzl='" + sfczckfhwggptzl + "' ";
                }
                if (!string.IsNullOrEmpty(wdwhjgfjgqsyyrkrgbdj))
                {
                    strWhere += " and wdwhjgfjgqsyyrkrgbdj ='" + wdwhjgfjgqsyyrkrgbdj + "' ";
                }
                if (!string.IsNullOrEmpty(jsrysstd))
                {
                    strWhere += " and jsrysstd='" + jsrysstd + "'  ";
                }
                if (!string.IsNullOrEmpty(cs) && cs != "0")
                {
                    strWhere += " and cs in (" + cs + ") ";
                }


                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string sql = "select  ROW_NUMBER() OVER ()index,* from (select * from (select ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.xzq,t1.xzqdm,t1.towndm,t1.town,t1.xzq,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh,t1.cs,t1.jsrysstd,t1.create_on,t1.create_name,t1.yhly,case when t1.wdwhjgfjgqsyyrkrgbdj=1 then '是' when t1.wdwhjgfjgqsyyrkrgbdj='0' then '否' end wdwhjgfjgqsyyrkrgbdj,\r\ncase when t1.sstdsyyrkrclzx=1 then '是' when t1.sstdsyyrkrclzx='0' then '否' end sstdsyyrkrclzx,\r\ncase when t1.gryssdcsszblyyqksddp=1 then '是' when t1.gryssdcsszblyyqksddp='0' then '否' end gryssdcsszblyyqksddp,\r\ncase when t1.fwyfwdddzxccdsssfayqjshsf=1 then '是' when t1.fwyfwdddzxccdsssfayqjshsf='0' then '否' end fwyfwdddzxccdsssfayqjshsf,\r\ncase when use_electric_bicycle=1 then '是' when use_electric_bicycle='0' then '否' end use_electric_bicycle,\r\ncase when use_bottled_liquefied_gas=1 then '是' when use_bottled_liquefied_gas='0' then '否' end use_bottled_liquefied_gas,\r\ncase when use_stacked_items=1 then '是' when use_stacked_items='0' then '否' end use_stacked_items,\r\ncase when mhqwbcwhyx=1 then '是' when mhqwbcwhyx='0' then '否' end mhqwbcwhyx,\r\ncase when jyplmhxtwbcwhyx='1' then '是' when jyplmhxtwbcwhyx='0' then '否' end jyplmhxtwbcwhyx,\r\ncase when ygbjwfwhyx='1' then '是' when ygbjwfwhyx='0' then '否' end ygbjwfwhyx,\r\ncase when slljdx='1' then '是' when slljdx='0' then '否' end slljdx,\r\ncase when sfczckfhwggptzl='1' then '是' when sfczckfhwggptzl='0' then '否' end sfczckfhwggptzl,notes from zm_pc_baselog_sqjz t1,zm_pc_yhlog_sqjz t2 where t1.id=t2.base_id and t1.yhly='"+yhly+ "' and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' ) t" +
               "   where  row_num=1 and yhly='"+yhly+"' and create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59'  " +
               "and fwbh not in(select DISTINCT fwbh from "+table_fwzt+" where  final_fwzt IS NOT NULL and final_create_on = '"+endTime+" 23:59:59' )  " +
               " " + strWhere + " order by create_on desc,xzqdm ,village asc ) t ";
                if (cked == "true")
                {
                    sql = "select  ROW_NUMBER() OVER ()index,* from (select * from (select ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.xzqdm,t1.towndm,t1.town,t1.xzq,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh,t1.cs,t1.jsrysstd,t1.create_on,t1.create_name,t1.yhly,case when t1.wdwhjgfjgqsyyrkrgbdj=1 then '是' when t1.wdwhjgfjgqsyyrkrgbdj='0' then '否' end wdwhjgfjgqsyyrkrgbdj,\r\ncase when t1.sstdsyyrkrclzx=1 then '是' when t1.sstdsyyrkrclzx='0' then '否' end sstdsyyrkrclzx,\r\ncase when t1.gryssdcsszblyyqksddp=1 then '是' when t1.gryssdcsszblyyqksddp='0' then '否' end gryssdcsszblyyqksddp,\r\ncase when t1.fwyfwdddzxccdsssfayqjshsf=1 then '是' when t1.fwyfwdddzxccdsssfayqjshsf='0' then '否' end fwyfwdddzxccdsssfayqjshsf,\r\ncase when use_electric_bicycle=1 then '是' when use_electric_bicycle='0' then '否' end use_electric_bicycle,\r\ncase when use_bottled_liquefied_gas=1 then '是' when use_bottled_liquefied_gas='0' then '否' end use_bottled_liquefied_gas,\r\ncase when use_stacked_items=1 then '是' when use_stacked_items='0' then '否' end use_stacked_items,\r\ncase when mhqwbcwhyx=1 then '是' when mhqwbcwhyx='0' then '否' end mhqwbcwhyx,\r\ncase when jyplmhxtwbcwhyx='1' then '是' when jyplmhxtwbcwhyx='0' then '否' end jyplmhxtwbcwhyx,\r\ncase when ygbjwfwhyx='1' then '是' when ygbjwfwhyx='0' then '否' end ygbjwfwhyx,\r\ncase when slljdx='1' then '是' when slljdx='0' then '否' end slljdx,\r\ncase when sfczckfhwggptzl='1' then '是' when sfczckfhwggptzl='0' then '否' end sfczckfhwggptzl,notes from zm_pc_baselog_sqjz t1,zm_pc_yhlog_sqjz t2 where t1.id=t2.base_id and t1.yhly='" + yhly + "' and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' ) t" +
                 "   where row_num=1 and yhly='" + yhly + "' and create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59'  " +
                 "and fwbh not in(select DISTINCT fwbh from "+table_fwzt+" where  final_fwzt IS NOT NULL and final_create_on = '"+endTime+" 23:59:59')  " +
                 " and village in(select villagedm from zdcmd) " + strWhere + " order by create_on desc,xzqdm ,village asc ) t ";

                }

                    //写入缓存
                    var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("专线信息统计");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("层数");
                row.CreateCell(8).SetCellValue("是否满足“双通道”要求");
                row.CreateCell(9).SetCellValue("是否存在彩钢板建筑出租住人");
                row.CreateCell(10).SetCellValue("是否存在窗口防护网、广告牌、铁栅栏");
                row.CreateCell(11).SetCellValue("电动自行车及蓄电池是否在室内停放充电");
                row.CreateCell(12).SetCellValue("服务于房屋的电动自行车充电设施是否按要求建设");
                row.CreateCell(13).SetCellValue("是否违规使用液化石油气瓶");
                row.CreateCell(14).SetCellValue("疏散通道是否堆放杂物");
                row.CreateCell(15).SetCellValue("是否私拉乱接电线");
                row.CreateCell(16).SetCellValue("其他高风险火灾隐患问题");
                row.CreateCell(17).SetCellValue("核验时间");
                row.CreateCell(18).SetCellValue("核验人员");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "towndm")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cs")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jsrysstd")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "wdwhjgfjgqsyyrkrgbdj")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "sfczckfhwggptzl")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_electric_bicycle")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwyfwdddzxccdsssfayqjshsf")
                            {
                                row.CreateCell(12).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_bottled_liquefied_gas")
                            {
                                row.CreateCell(13).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_stacked_items")
                            {
                                row.CreateCell(14).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "slljdx")
                            {
                                row.CreateCell(15).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "notes")
                            {
                                row.CreateCell(16).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "create_on")
                            {
                                row.CreateCell(17).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "create_name")
                            {
                                row.CreateCell(18).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "专项信息统计情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 导出人员密集场所
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("export/rymjcs/exportrymjcs")]
        [Produces("application/octet-stream")]
        public IActionResult exportrymjcs([FromQuery] string xzqdm, [FromQuery] string towndm)
        {
            try
            {

                var userNumber = this.CurrentUser();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

                string strWhere = "";

                if (user.RoleType == 4)
                {
                    strWhere += "and towndm ='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 3) 
                {
                    strWhere += "and xzqdm ='" + model.RegionCode + "'";
                }

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";

                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;

                string sql = @"select ROW_NUMBER() OVER() AS idex,* from (select n1.xzq name,n1.xzqdm,  sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%餐饮%'::text) THEN 1 ELSE 0 END) AS cy_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%商店%'::text) THEN 1 ELSE 0 END) AS sd_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美容美发%'::text) THEN 1 ELSE 0 END) AS mrmf_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%KTV%'::text) THEN 1 ELSE 0 END) AS ktv_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%网吧%'::text) THEN 1 ELSE 0 END) AS wb_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美甲美睫%'::text) THEN 1 ELSE 0 END) AS mjmj_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%棋牌室%'::text) THEN 1 ELSE 0 END) AS qps_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%茶社%'::text) THEN 1 ELSE 0 END) AS cs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%足疗、按摩%'::text) THEN 1 ELSE 0 END) AS zlam_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%教学培训%'::text) THEN 1 ELSE 0 END) AS jxpx_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%医疗诊所%'::text) THEN 1 ELSE 0 END) AS ylzs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%加工生产%'::text) THEN 1 ELSE 0 END) AS jgsc_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%其它%'::text) THEN 1 ELSE 0 END) AS qt_count   
                from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   
                where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and  cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from " + table_fwzt+" where final_fwzt is not null and   final_create_on='"+ today + " 23:59:59' ) group by n1.xzq,n1.xzqdm order by n1.xzqdm) t where 1=1 " + strWhere;

                if (!string.IsNullOrEmpty(xzqdm)) 
                {
                 sql = @"select ROW_NUMBER() OVER() AS idex,* from (select n1.xzq,n1.xzqdm, n2.towndm,n2.town name,  sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%餐饮%'::text) THEN 1 ELSE 0 END) AS cy_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%商店%'::text) THEN 1 ELSE 0 END) AS sd_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美容美发%'::text) THEN 1 ELSE 0 END) AS mrmf_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%KTV%'::text) THEN 1 ELSE 0 END) AS ktv_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%网吧%'::text) THEN 1 ELSE 0 END) AS wb_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美甲美睫%'::text) THEN 1 ELSE 0 END) AS mjmj_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%棋牌室%'::text) THEN 1 ELSE 0 END) AS qps_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%茶社%'::text) THEN 1 ELSE 0 END) AS cs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%足疗、按摩%'::text) THEN 1 ELSE 0 END) AS zlam_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%教学培训%'::text) THEN 1 ELSE 0 END) AS jxpx_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%医疗诊所%'::text) THEN 1 ELSE 0 END) AS ylzs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%加工生产%'::text) THEN 1 ELSE 0 END) AS jgsc_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%其它%'::text) THEN 1 ELSE 0 END) AS qt_count   
                from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   
                where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and  cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from " + table_fwzt+" where final_fwzt is not null and   final_create_on='"+ today + " 23:59:59' ) group by n1.xzq,n1.xzqdm, n2.towndm,n2.town order by n2.towndm) t where 1=1 and xzqdm='" + xzqdm+"'";
                }else if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                    sql = @"select ROW_NUMBER() OVER() AS idex,* from (select n1.xzq,n1.xzqdm, n2.towndm,n2.town , n2.villagedm,n2.village name,  sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%餐饮%'::text) THEN 1 ELSE 0 END) AS cy_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%商店%'::text) THEN 1 ELSE 0 END) AS sd_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美容美发%'::text) THEN 1 ELSE 0 END) AS mrmf_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%KTV%'::text) THEN 1 ELSE 0 END) AS ktv_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%网吧%'::text) THEN 1 ELSE 0 END) AS wb_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美甲美睫%'::text) THEN 1 ELSE 0 END) AS mjmj_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%棋牌室%'::text) THEN 1 ELSE 0 END) AS qps_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%茶社%'::text) THEN 1 ELSE 0 END) AS cs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%足疗、按摩%'::text) THEN 1 ELSE 0 END) AS zlam_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%教学培训%'::text) THEN 1 ELSE 0 END) AS jxpx_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%医疗诊所%'::text) THEN 1 ELSE 0 END) AS ylzs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%加工生产%'::text) THEN 1 ELSE 0 END) AS jgsc_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%其它%'::text) THEN 1 ELSE 0 END) AS qt_count   
                from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   
                where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and  cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from " + table_fwzt+" where final_fwzt is not null and   final_create_on='"+ today + " 23:59:59')  group by n1.xzq,n1.xzqdm,n2.towndm,n2.town, n2.villagedm,n2.village order by n2.villagedm) t where 1=1 " + strWhere;

                }

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("人员密集场所信息统计");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("名称");
                row.CreateCell(2).SetCellValue("餐饮");
                row.CreateCell(3).SetCellValue("商店");
                row.CreateCell(4).SetCellValue("美容美发");
                row.CreateCell(5).SetCellValue("KTV");
                row.CreateCell(6).SetCellValue("网吧");
                row.CreateCell(7).SetCellValue("美甲美睫");
                row.CreateCell(8).SetCellValue("棋牌室");
                row.CreateCell(9).SetCellValue("茶社");
                row.CreateCell(10).SetCellValue("足疗、按摩");
                row.CreateCell(11).SetCellValue("教学培训");
                row.CreateCell(12).SetCellValue("医疗诊所");
                row.CreateCell(13).SetCellValue("加工生产");
                row.CreateCell(14).SetCellValue("其它");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "idex")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "name")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cy_count")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "sd_count")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "mrmf_count")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "ktv_count")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "wb_count")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "mjmj_count")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "qps_count")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cs_count")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zlam_count")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jxpx_count")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "ylzs_count")
                            {
                                row.CreateCell(12).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jgsc_count")
                            {
                                row.CreateCell(13).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "qt_count")
                            {
                                row.CreateCell(14).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "人员密集场所统计情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        /// <summary>
        /// 导出人员密集场所明细
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("export/rymjcs/exportrymjcsmx")]
        [Produces("application/octet-stream")]
        public IActionResult exportrymjcsMx([FromQuery] string xzqdm, [FromQuery] string towndm)
        {
            try
            {

                var userNumber = this.CurrentUser();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

                string strWhere = "";

                if (user.RoleType == 4 || user.RoleType == 3)
                {
                    strWhere += "and xzqdm ='" + model.RegionCode + "'";
                }
                if (!string.IsNullOrEmpty(xzqdm)) 
                {
                    strWhere += "and xzqdm ='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += "and towndm ='" + towndm + "'";
                }

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";

                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;

                string sql = @"select ROW_NUMBER() OVER() AS idex,* from (select n1.xzq,n1.xzqdm, n2.towndm,n2.town, n2.villagedm,n2.village,n1.hzxm,n1.address,n1.fwbh,cjzwqtjyytmsg
			          from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and 
                        cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from " + table_fwzt+" where final_fwzt is not null and   final_create_on='"+ today + " 23:59:59') order by n2.villagedm ) t where 1=1  " + strWhere;


                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("人员密集场所信息统计");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("经营液态");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "idex")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cjzwqtjyytmsg")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "人员密集场所明细情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出核验明细
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("export/hymx/exporthymx")]
        [Produces("application/octet-stream")]
        public IActionResult exporthymx([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string yhly, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cs, [FromQuery] string nickname, [FromQuery] string filed)
        {
            try
            {

                var userNumber = this.CurrentUser();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

                string strWhere = "";

                if (user.RoleType == 3)
                {
                    strWhere += " and xzq='" + model.RegionName + "'";
                }
                else if (user.RoleType == 4)
                {
                    towndm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        towndm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = DateTime.Now.ToShortDateString();
                    endTime = DateTime.Now.ToShortDateString();
                }
                string sqlWhere = "";
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "'";
                    sqlWhere += " and m1.xzqdm='" + xzqdm + "' and m1.town='" + towndm + "' and m1.village='" + villagedm + "'";
                }
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "'";
                    sqlWhere += " and m1.xzqdm='" + xzqdm + "' and m1.town='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                    sqlWhere += " and m1.xzqdm='" + xzqdm + "' ";
                }
                if (string.IsNullOrEmpty(yhly))
                {
                    yhly = "0";
                }
                if (!string.IsNullOrEmpty(nickname))
                {
                    strWhere += " and create_name  like '%" + nickname + "%' ";
                }
                if (!string.IsNullOrEmpty(cs) && cs != "0")
                {
                    strWhere += " and cs in (" + cs + ") ";
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string where = "";
                if (!string.IsNullOrEmpty(cs))
                {
                    if (cs.Contains("3"))
                    {
                        string[] arrr = cs.Split(',');
                        if (arrr.Length == 2)
                        {
                            string cssql = "";
                            for (int k = 0; k < arrr.Length; k++)
                            {
                                if (arrr[k] == "3")
                                {
                                    cssql += "  cs >=3 OR ";
                                }
                                if (arrr[k] == "1")
                                {
                                    cssql += "  cs=1 OR ";
                                }
                                if (arrr[k] == "2")
                                {
                                    cssql += " cs =2 OR ";
                                }
                            }

                            where += " and (" + (cssql.Substring(0, cssql.Length - 3)) + ")";
                        }
                        else
                        {
                            where += " and cs >=" + cs;
                        }
                    }
                    else
                    {
                        where += " and cs in(" + cs + ")";
                    }
                }
                string sql = "";
                if (filed == "total")
                {
                    sql = "select  ROW_NUMBER() OVER ()index ,*from (select t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh " +
                       " from zm_buildings t1 where \"CreateOn\" <='" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) )   " + where + " order by village) t where  1=1  " + where;
                }
                else if (filed == "hccount")
                {
                    sql = @" select ROW_NUMBER() OVER ()index ,*   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,create_on,create_name,create_by, case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                      "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + yhly + "' and (m1.create_on) " +
                      "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 and yhly=" + yhly + " and (create_on) " +
                      "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' )) ) " +
                      " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1  " + where;
                    //   sql = "select ROW_NUMBER() OVER ()index ,* from (select * from temp1 order by create_name desc,village desc, create_on desc  limit 200 offset 0 )  t ";

                }
                else if (filed == "whccount")
                {
                    sql = "select  ROW_NUMBER() OVER ()index ,*from (select t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh " +
                       " from zm_buildings t1 where \"CreateOn\" <='" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ))  " +
                       "  and fwbh not in(" +
                       " select fwbh from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num,   m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + yhly + "'" +
                       " and (m1.create_on)   between '" + startTime + "' and '" + endTime + " 23:59:59'   " + sqlWhere + " ) t1  WHERE  row_num=1 and yhly=" + yhly + " and (create_on)   between '" + startTime + "' and '" + endTime + " 23:59:59' " +
                       " " + strWhere + "  and fwbh not in(	 select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' )) )) t where 1=1  " + where ;

                }
                else if (filed == "ydbcount")
                {
                    sql = @" select ROW_NUMBER() OVER ()index ,*   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,create_on,create_name,create_by,jsrysstd, case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                      "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + yhly + "' and (m1.create_on) " +
                      "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 and yhly=" + yhly + " and (create_on) " +
                      "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) ) " +
                      " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1 and jsrysstd='是' " + where;
                }
                else if (filed == "wdbcount")
                {
                    sql = @" select ROW_NUMBER() OVER ()index ,*   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,create_on,create_name,create_by,jsrysstd, case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                      "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id and m1.yhly='" + yhly + "' and (m1.create_on) " +
                      "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 and yhly=" + yhly + " and (create_on) " +
                      "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) ) " +
                      " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1 and (jsrysstd ='否' OR jsrysstd is null ) " + where ;

                }
                else if (filed == "yhcount")
                {
                    sql = "select ROW_NUMBER() OVER ()index ,*  from (SELECT  m1.xzqdm,m1.xzq,m1.town towndm,m1.towndm town,m1.village villagedm,m1.villagedm village,hzxm,address,m1.fwbh ,cs ,m1.create_on," +
                        "check_id,check_name,check_sm,check_by,t3.nickname,case when zg_value=1 then '已整改' else '未整改' end zg_value,case when jczt='2' then '已复合通过' when jczt='1' then '待复核' else '未复核'" +
                        " end jczt,case when jcly='3' then '区级核验' when jcly='2' then '市级核验'   else '乡镇核验' end yhly   FROM zm_pc_yhlog_dx_sqjz m1 left join zm_pc_base t2 on m1.fwbh=t2.fwbh left join zm_user_cj t3 on m1.check_by=t3.\"number\"  WHERE m1.create_on between '" + startTime + "' " +
                        "and '" + endTime + " 23:59:59'    " + sqlWhere + "  AND jcly = " + yhly + " and m1.fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) order by villagedm, create_on desc) t where 1=1  " + where;
                }
                else if (filed == "zgcount")
                {
                    sql = "select ROW_NUMBER() OVER ()index ,*  from (SELECT  m1.xzqdm,m1.xzq,m1.town towndm,m1.towndm town,m1.village villagedm,m1.villagedm village,hzxm,address,m1.fwbh ,cs ,m1.create_on," +
                        "check_id,check_name,check_sm,check_by,t3.nickname,case when zg_value=1 then '已整改' else '未整改' end zg_value,case when jczt='2' then '已复合通过' when jczt='1' then '待复核' else '未复核'" +
                        " end jczt,case when jcly='3' then '区级核验' when jcly='2' then '市级核验'   else '乡镇核验' end yhly   FROM zm_pc_yhlog_dx_sqjz m1 left join zm_pc_base t2 on m1.fwbh=t2.fwbh left join zm_user_cj t3 on m1.check_by=t3.\"number\"  WHERE m1.create_on between '" + startTime + "' " +
                        "and '" + endTime + " 23:59:59'    " + sqlWhere + "  AND jcly = " + yhly + " and m1.fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) order by villagedm, create_on desc) t where 1=1  and zg_value='已整改'  " + where;
                }
                else if (filed == "sycount")
                {
                    sql = "select ROW_NUMBER() OVER ()index ,*  from (SELECT  m1.xzqdm,m1.xzq,m1.town towndm,m1.towndm town,m1.village villagedm,m1.villagedm village,hzxm,address,m1.fwbh ,cs ,m1.create_on," +
                        "check_id,check_name,check_sm,check_by,t3.nickname,case when zg_value=1 then '已整改' else '未整改' end zg_value,case when jczt='2' then '已复合通过' when jczt='1' then '待复核' else '未复核'" +
                        " end jczt,case when jcly='3' then '区级核验' when jcly='2' then '市级核验'   else '乡镇核验' end yhly   FROM zm_pc_yhlog_dx_sqjz m1 left join zm_pc_base t2 on m1.fwbh=t2.fwbh left join zm_user_cj t3 on m1.check_by=t3.\"number\"  WHERE m1.create_on between '" + startTime + "' " +
                        "and '" + endTime + " 23:59:59'    " + sqlWhere + "  AND jcly = " + yhly + " and m1.fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ) ) order by villagedm, create_on desc) t where 1=1  and zg_value='未整改'  " + where;
                }


                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("核验情况");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");

                row.CreateCell(7).SetCellValue("核验状态");
                row.CreateCell(8).SetCellValue("隐患项");
                row.CreateCell(9).SetCellValue("核验时间");
                row.CreateCell(10).SetCellValue("核验人员姓名");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "towndm")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cjzwqtjyytmsg")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "yhzt")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "check_name")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "create_on")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "create_name")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "核验情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 导出房主自查情况（已自查、未自查）
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("export/zc/zcqkExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult zcqkExportExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string category, [FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord, [FromQuery] string cs)
        {
            try
            {
                string strWhere = " ";
                string strWherezc = " ";
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                    strWherezc += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                    strWherezc += " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += "  and towndm='" + towndm + "' ";
                    strWherezc += "  and town='" + towndm + "' ";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                    strWherezc += " and village='" + villagedm + "'";
                }
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                string sql = " ";
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;

                    if (Convert.ToDateTime(endTime) > DateTime.Now)
                    {
                        table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                    }
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                
                DateTime start = Convert.ToDateTime(startTime);
                DateTime end = Convert.ToDateTime(endTime);
                string sheetName = "";
                if (category == "yzc")
                {
                    sheetName = "已自查";
                    //if (start.Year == end.Year && start.Month == end.Month)
                    //{
                    //    sql = @"select ROW_NUMBER() OVER ()index ,* from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 " +
                    //  "    where   row_num=1  and fwbh not in " +
                    //  " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere;
                    //}
                    //else if (start.Year == end.Year && (end.Month - start.Month) == 1)
                    //{
                    //    sql = @"select ROW_NUMBER() OVER ()index ,* from (select ROW_NUMBER() OVER(PARTITION BY fwbh ORDER BY create_on DESC) AS rn,* from (select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) "
                    //        + "    AS row_num,t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh,t1.create_on from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 "
                    //       + "    where   row_num=1  and fwbh not in  (select fwbh from "+ table_fwzt + " where final_fwzt IS NOT NULL AND "
                    //       + " final_create_on = '" + endTime + " 23:59:59')   " + strWhere
                    //       + "  union all "
                    //       + " select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh,t1.create_on from zm_pc_baselog_" + start.Year + "_0" + start.Month + " t1,zm_pc_yhlog_" + start.Year + "_0" + start.Month + " t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1  "
                    //       + " where   row_num=1  and fwbh not in  (select fwbh from "+ table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59')   "
                    //       + strWhere + "     ) t order by create_on desc) t  where rn=1 ";
                    //}
                    //else 
                    {
                        sql = @"select ROW_NUMBER() OVER ()index ,* from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh,t1.create_on from zm_pc_baselog t1,zm_pc_yhlog t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 " +
                 "    where  row_num=1  and fwbh not in " +
                 " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere;
                    }

                }
                else if (category == "wzc")
                {
                    sheetName = "未自查";
                    //if (start.Year == end.Year && start.Month == end.Month)
                    //{

                    //    sql = @" select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                    //     " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                    //    "  and fwbh not in( " +
                    //    " select fwbh from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 " +
                    //  "    where  row_num=1  and fwbh not in " +
                    //  " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') )  order by village ) t  where 1=1  "+ strWhere;
                    //}
                    //else if (start.Year == end.Year && (end.Month - start.Month) == 1)
                    //{
                    //    sql = @"select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                    //     " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                    //    "  and fwbh not in( select fwbh from (select ROW_NUMBER() OVER(PARTITION BY fwbh ORDER BY create_on DESC) AS rn,* from (select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) "
                    //        + "    AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 "
                    //       + "    where   row_num=1  and fwbh not in  (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND "
                    //       + " final_create_on = '" + endTime + " 23:59:59')   " + strWherezc
                    //       + "  union all "
                    //       + " select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + start.Year + "_0" + start.Month + " t1,zm_pc_yhlog_" + start.Year + "_0" + start.Month + " t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1  "
                    //       + " where  row_num=1  and fwbh not in  (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59')   "
                    //       + strWherezc + "     ) t order by create_on desc) t  where rn=1 )  order by village ) t where 1=1 " + strWhere;
                    //}
                    //else
                    {
                        sql = @"select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                         " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                        "  and fwbh not in(select fwbh from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog t1,zm_pc_yhlog t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 " +
                 "    where   row_num=1  and fwbh not in " +
                 " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWherezc + ")  order by village ) t where  1=1  " + strWhere;
                    }


                }

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("房主自查情况（"+ sheetName + "）");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "房主自查情况（"+ sheetName + "）" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出未悬挂固定警示标牌
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("export/xgjsbp/exportNoxgjsbplist")]
        [Produces("application/octet-stream")]
        public IActionResult exportNoxgjsbplist([FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord)
        {
            try
            {

                var userNumber = this.CurrentUser();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

                string strWhere = "";

            
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "" + startTime + "";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }

                string where = "";
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.towndm='" + towndm + "' and t3.villagedm='" + villagedm + "'";
                    where = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') and  (villagedm='" + villagedm + "')";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.towndm='" + towndm + "' ";
                    where = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') ";
                }
                else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and t3.xzqdm='" + xzqdm + "' ";
                    where = " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    where += " and (hzxm like '%" + txtKeyWord + "%' OR address like '%" + txtKeyWord + "%' OR fwbh like '%" + txtKeyWord + "%') ";
                }
                string toDay = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(toDay);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string sql = "";
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                if (startTime == "2024-01-01" && endTime == today)
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select xzq,xzqdm,town,towndm,village,villagedm,hzxm,address,fwbh,xggdjsbp  from "
        + " (select t1.* from ( SELECT t3.* FROM(SELECT   t1.ID,t1.xzq, t1.xzqdm,t1.towndm town,t1.town towndm,t1.villagedm village,t1.village villagedm,hzxm,address,fwbh, "
        + " cs,create_on,xggdjsbp FROM zm_pc_base t1  WHERE create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59'    ) t3  INNER JOIN zm_pc_yh m2 ON t3.ID = m2.base_id "
        + " WHERE t3.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' and xggdjsbp=0    " + strWhere + "    AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " "
        + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) ) ) t1 ORDER BY villagedm )  t  where 1=1   " + where
        + " union all"
        + " select xzq,xzqdm,town,towndm,village,villagedm,hzxm,address,fwbh,'0' xggdjsbp from zm_buildings where fwbh not in( select DISTINCT fwbh from " + table_fwzt + " where  "
        + " (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) ) and fwbh not in(select fwbh  from (select t1.* from ( SELECT t3.* FROM(SELECT   t1.ID,t1.xzq, "
        + " t1.xzqdm,t1.towndm town,t1.town towndm,t1.villagedm village,t1.village villagedm,hzxm,address,fwbh, cs,create_on,xggdjsbp FROM zm_pc_base t1  WHERE create_on BETWEEN '" + startTime + "' "
        + " and '" + endTime + " 23:59:59'    ) t3  INNER JOIN zm_pc_yh m2 ON t3.ID = m2.base_id  WHERE t3.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59'  " + strWhere
        + " AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) )  ) t1 )  t  "
        + " where 1=1 " + where + "  ORDER BY villagedm ) " + where + ") t where 1=1 " + where;
                }
               

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("未悬挂固定警示标牌情况");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("悬挂固定警示标牌数量");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "xggdjsbp")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "未悬挂固定警示标牌情况" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 导出核验情况（已核验、未核验）
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("export/hc/hcqkExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult zcqkExportExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string category, [FromQuery] string role_type, [FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord, [FromQuery] string cs)
        {
            try
            {
                string strWhere = " ";
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += "  and towndm='" + towndm + "' ";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                }
                if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                string sql = " ";
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }

                DateTime start = Convert.ToDateTime(startTime);
                DateTime end = Convert.ToDateTime(endTime);
                string sheetName = "";
                if (category == "yhy")
                {
                    sheetName = "已核验";
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh,t1.create_on from zm_pc_baselog_sqjz t1,zm_pc_yhlog_sqjz t2 where t1.id=t2.base_id and t1.yhly='" + role_type + "' and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' ) t1 " +
             "    where  create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' and row_num=1  and fwbh not in " +
             " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere ;

                }
                else if (category == "wzc")
                {
                    sheetName = "未核验";
                    sql = @"select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                    " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                   "  and fwbh not in(select fwbh from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.fwbh,t1.xzqdm,t1.town towndm,t1.village villagedm,t1.create_on from zm_pc_baselog_sqjz t1,zm_pc_yhlog_sqjz t2 where t1.id=t2.base_id and t1.yhly='" + role_type + "' and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 " +
            "    where  create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' and row_num=1  and fwbh not in " +
            " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere + ")  order by village ) t where  1=1  " + strWhere;


                }

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("核验情况（" + sheetName + "）");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "核验情况（" + sheetName + "）" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 导出检查情况（已检查、未检查）
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("export/static/GetJcYhMxExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult GetJcYhMxExport([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string category, [FromQuery] string role_type, [FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord, [FromQuery] string cs)
        {
            try
            {
                string strWhere = " ";
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }
                string cj_strWhere = " ";
                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "' ";
                    cj_strWhere += " and t1.xzqdm='" + xzqdm + "' ";

                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += "  and towndm='" + towndm + "' ";
                    cj_strWhere += " and t1.town='" + towndm + "' ";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                    cj_strWhere += " and t1.village='" + villagedm + "' ";
                }
                if (string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    startTime = "2024-01-01";
                    endTime = DateTime.Now.ToString("yyyy-MM-dd");
                }
                string sql = " ";
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                if (!string.IsNullOrEmpty(cs))
                {
                    if (cs.Contains("3"))
                    {
                        string[] arrr = cs.Split(',');
                        if (arrr.Length == 2)
                        {
                            string cssql = "";
                            for (int ki = 0; ki < arrr.Length; ki++)
                            {
                                if (arrr[ki] == "3")
                                {
                                    cssql += "  cs >=3 OR ";
                                }
                                if (arrr[ki] == "1")
                                {
                                    cssql += "  cs=1 OR ";
                                }
                                if (arrr[ki] == "2")
                                {
                                    cssql += " cs =2 OR ";
                                }
                            }

                            strWhere += " and (" + (cssql.Substring(0, cssql.Length - 3)) + ")";
                        }
                        else
                        {
                            strWhere += " and cs >=" + cs;
                        }
                    }
                    else
                    {
                        strWhere += " and cs in(" + cs + ")";
                    }
                }
                DateTime start = Convert.ToDateTime(startTime);
                DateTime end = Convert.ToDateTime(endTime);
                string sheetName = "";
                if (category == "yjc")
                {
                    sheetName = "已检查";

                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.town towndm,t1.towndm town,t1.villagedm village,t1.village villagedm,t1.hzxm,t1.address,t1.fwbh,
t1.create_on from zm_pc_baselog_cj t1,zm_pc_yhlog_cj t2 where t1.id=t2.base_id and t1.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t1 " +
             "    where  create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' and row_num=1  and fwbh not in " +
             " (select distinct fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere;

                }
                else if (category == "wjc")
                {
                    sheetName = "未检查";
                    sql = @"select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                     " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                    "  and fwbh not in(select distinct t1.fwbh from zm_pc_baselog_cj t1,zm_pc_yhlog_cj t2 where t1.id=t2.base_id  and t1.create_on  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' " + cj_strWhere + " and t1.fwbh not in  (select distinct fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on ='" + endTime + " 23:59:59'))  order by village ) t where  1=1  " + strWhere ;
                }
                else if (category == "yhtotal")
                {
                    sheetName = "隐患情况";
                    sql = @$"  select ROW_NUMBER() OVER ()index, * from (" +
         "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue,zg_time from " +
         "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
         "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=0 then  '未整改' else '已整改' end zgvalue,zg_time from zm_pc_yhlog_dx_cj t1 left join zm_pc_yhlog_dxzgson_cj t3 on  t1.\"id\"=t3.yhlog_dx_id " +
         " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings where create_on  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
         "  final_create_on='" + endTime + " 23:59:59')   " + strWhere +
                    "   ) t where 1=1      ";
                }
                else if (category == "zgyh")
                {
                    sheetName = "隐患整改情况";
                    sql = @$"  select ROW_NUMBER() OVER ()index, * from (" +
         "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue,zg_time from " +
         "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
         "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=0 then  '未整改' else '已整改' end zgvalue,zg_time from zm_pc_yhlog_dx_cj t1 left join zm_pc_yhlog_dxzgson_cj t3 on  t1.\"id\"=t3.yhlog_dx_id " +
         " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings where zg_time  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
         "  final_create_on='" + endTime + " 23:59:59')   " + strWhere +
                    "   ) t where 1=1  and zgvalue='已整改'    ";
                }
                else if (category == "syyh")
                {
                    sheetName = "剩余隐患";
                    sql = @$"  select ROW_NUMBER() OVER ()index, * from (" +
          "    select xzq,xzqdm,towndm town,town towndm, villagedm village,village villagedm,hzxm,address,fwbh,check_id,check_name,create_on,'村级核验'  yhly,create_name,zgvalue,zg_time from " +
          "  (select distinct *  from (select t1.id, t1.xzqdm,t1.xzq,t1.town towndm,t1.towndm town,t1.village villagedm,t1.villagedm village,t2.hzxm,t2.address,t1.fwbh,t1.zg_value," +
          "  t1.create_on,t1.check_name, t1.jcly,t1.check_id,t1.check_nickname create_name,case when zg_value=0 then  '未整改' else '已整改' end zgvalue,zg_time from zm_pc_yhlog_dx_cj t1 left join zm_pc_yhlog_dxzgson_cj t3 on  t1.\"id\"=t3.yhlog_dx_id " +
          " left join zm_buildings t2 on t1.fwbh=t2.fwbh) zm_buildings where create_on  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59') t2  where fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and " +
          "  final_create_on='" + endTime + " 23:59:59')   " + strWhere +
                     "   ) t where 1=1  and zgvalue='未整改'    ";
                }

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("检查情况（" + sheetName + "）");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("户主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                if (category == "yhtotal" || category == "syyh" || category == "zgyh") 
                {
                    row.CreateCell(6).SetCellValue("隐患项名称");
                    row.CreateCell(7).SetCellValue("是否整改");
                    row.CreateCell(8).SetCellValue("检查时间");
                    row.CreateCell(9).SetCellValue("检查人员");
                }
                    // 第二行 写数据
                    int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            if (category == "yhtotal" || category == "syyh" || category == "zgyh")
                            {
                                if (kvp.Key == "check_name")
                                {
                                    row.CreateCell(7).SetCellValue(kvp.Value + "");
                                }
                                else if (kvp.Key == "zgvalue")
                                {
                                    row.CreateCell(8).SetCellValue(kvp.Value + "");
                                }
                                else if (kvp.Key == "create_on")
                                {
                                    row.CreateCell(9).SetCellValue(kvp.Value + "");
                                }
                                else if (kvp.Key == "create_name")
                                {
                                    row.CreateCell(10).SetCellValue(kvp.Value + "");
                                }
                            }
                            }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村级检查情况（" + sheetName + "）" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出该任务下所有信息
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/task/taskExportbytaskid")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult taskExportbytaskid([FromQuery] string taskid, [FromQuery] string taskname)
        {
            try
            {
                string sql = @"SELECT ROW_NUMBER( ) OVER ( ) INDEX,* FROM (
	SELECT T.*,M.create_on yh_create_on,M.create_name yh_create_name,M.jsrysstd,CASE
			
			WHEN M.wdwhjgfjgqsyyrkrgbdj = 1 THEN
			'是' 
			WHEN M.wdwhjgfjgqsyyrkrgbdj = '0' THEN
			'否' 
		END wdwhjgfjgqsyyrkrgbdj,
CASE
	
	WHEN M.fwyfwdddzxccdsssfayqjshsf = 1 THEN
	'是' 
	WHEN M.fwyfwdddzxccdsssfayqjshsf = '0' THEN
	'否' 
	WHEN fwyfwdddzxccdsssfayqjshsf = '2'  THEN
	'不涉及' 
	WHEN   fwyfwdddzxccdsssfayqjshsf IS NULL  and hyzt='已核验' THEN
	'不涉及' 
	END fwyfwdddzxccdsssfayqjshsf,
CASE
	
	WHEN tt.sfczckfhwggptzl = '1' THEN
	'是' 
	WHEN tt.sfczckfhwggptzl = '0' THEN
	'否' 
	END sfczckfhwggptzl,
CASE
	
	WHEN use_electric_bicycle = 1 THEN
	'是' 
	WHEN use_electric_bicycle = '0' THEN
	'否' 
	END use_electric_bicycle,
CASE
	
	WHEN use_bottled_liquefied_gas = 1 THEN
	'是' 
	WHEN use_bottled_liquefied_gas = '0' THEN
	'否' 
	END use_bottled_liquefied_gas,
CASE
	
	WHEN use_stacked_items = 1 THEN
	'是' 
	WHEN use_stacked_items = '0' THEN
	'否' 
	END use_stacked_items,
CASE
	
	WHEN slljdx = '1' THEN
	'是' 
	WHEN slljdx = '0' THEN
	'否' 
	END slljdx,
	case WHEN notes = '1' THEN
'是' 
WHEN notes = '0'  THEN
'否' 
WHEN ( notes is null) and hyzt='已核验'  THEN
'否' 
END notes  FROM (SELECT t1.fwbh,
			t1.taskid,
			t1.baseid,
			t1.ybhnumber,(case when t1.hyzt=1 then '已核验' else '未核验' end)  hyzt,nickname,hzxm,address,t3.xzq,t3.town,t3.village,t3.villagedm,t1.cs,t3.address
		FROM (select * from zm_taskdetail where taskid='" + taskid + "')  t1 LEFT JOIN zm_task_zuser t2 ON t1.number = t2.number LEFT JOIN zm_buildings t3 ON t1.fwbh = t3.fwbh) T LEFT JOIN  zm_pc_baselog_sqjz M ON T.fwbh = M.fwbh  and T.baseid=M.id and T.ybhnumber=M.create_by  LEFT JOIN zm_pc_yhlog_sqjz tt ON T.fwbh = tt.fwbh AND T.baseid = tt.base_id  AND T.ybhnumber = tt.create_by   ORDER BY M.create_on) T  where 1=1 ";
                
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();

                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("抽检任务明细");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("房屋编号");
                row.CreateCell(5).SetCellValue("房屋地址");
                row.CreateCell(6).SetCellValue("房屋层数");
                row.CreateCell(7).SetCellValue("户主姓名");


                row.CreateCell(8).SetCellValue("核验状态");
                row.CreateCell(9).SetCellValue("是否满足双通道");

                row.CreateCell(10).SetCellValue("是否存在夹芯彩钢板出租住人");
                row.CreateCell(11).SetCellValue("服务于房屋的电动自行车充电设施是否按要求建设");
                row.CreateCell(12).SetCellValue("是否存在窗口防护网、广告牌、铁栅栏");
                row.CreateCell(13).SetCellValue("电动自行车及蓄电池是否在室内停放充电");
                row.CreateCell(14).SetCellValue("是否违规使用液化石油气瓶");
                row.CreateCell(15).SetCellValue("通道是否堆放杂物");
                row.CreateCell(16).SetCellValue("是否私拉乱接电线");
                row.CreateCell(17).SetCellValue("是否存在其他高风险火灾隐患问题");






                row.CreateCell(18).SetCellValue("核验时间");
                row.CreateCell(19).SetCellValue("核验人");


                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cs")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hyzt")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "jsrysstd")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "wdwhjgfjgqsyyrkrgbdj")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwyfwdddzxccdsssfayqjshsf")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }

                            else if (kvp.Key == "sfczckfhwggptzl")
                            {
                                row.CreateCell(12).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_electric_bicycle")
                            {
                                row.CreateCell(13).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "use_bottled_liquefied_gas")
                            {
                                row.CreateCell(14).SetCellValue(kvp.Value + "");
                            }



                            else if (kvp.Key == "use_stacked_items")
                            {
                                row.CreateCell(15).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "slljdx")
                            {
                                row.CreateCell(16).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "notes")
                            {
                                row.CreateCell(17).SetCellValue(kvp.Value + "");
                            }






                            else if (kvp.Key == "yh_create_on")
                            {
                                row.CreateCell(18).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "nickname")
                            {
                                row.CreateCell(19).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", taskname+"任务明细" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出该任务下所有信息
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/task/taskExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult taskExport([FromQuery] string taskid, [FromQuery] string hyzt, [FromQuery] string txtKeyWord, [FromQuery] string nickname, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string villagedm, [FromQuery] string jsrysstd)
        {
            try
            {
                var userNumber = this.CurrentUser();
                

                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
               
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
                string sql = "";
               

                string strWhere = "";
                if (!string.IsNullOrEmpty(hyzt))
                {
                    if (hyzt == "已核验")
                    {
                        strWhere += " and hyzt='1'";
                    }
                    else if (hyzt == "未核验")
                    {
                        strWhere += " and hyzt is null";
                    }
                }
                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    strWhere += " and (yh_create_on  between '" + startTime + "' and '" + endTime + " 23:59:59')";
                }
                if (!string.IsNullOrEmpty(nickname))
                {
                    strWhere += " and (nickname  like '%" + nickname + "%')";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and (villagedm  ='" + villagedm + "')";
                }
                if (!string.IsNullOrEmpty(jsrysstd) && jsrysstd!="undefined")
                {
                    strWhere += " and (jsrysstd  ='" + jsrysstd + "')";
                }
                 sql = @"SELECT ROW_NUMBER( ) OVER ( ) INDEX,* FROM (
	SELECT T.*,M.create_on yh_create_on,M.create_name yh_create_name,M.jsrysstd FROM (SELECT t1.fwbh,t1.taskid,	t1.baseid,t1.hyzt,nickname,hzxm,address,t3.xzq,t3.town,t3.village,t3.villagedm,t3.cs
		FROM (select * from zm_taskdetail where taskid='" + taskid + "')  t1 LEFT JOIN zm_task_zuser t2 ON t1.number = t2.number LEFT JOIN zm_buildings t3 ON t1.fwbh = t3.fwbh) T LEFT JOIN ( SELECT fwbh, create_on, create_name, jsrysstd,id FROM zm_pc_baselog_sqjz WHERE yhly = '2' AND iscj = 1 ) M ON T.fwbh = M.fwbh  and T.baseid=M.id) T  where 1=1 " + strWhere  ;
                
     
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();

                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("任务明细");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("房屋编号");
                row.CreateCell(5).SetCellValue("房屋地址");
                row.CreateCell(6).SetCellValue("房屋层数");
                row.CreateCell(7).SetCellValue("户主姓名");


                row.CreateCell(8).SetCellValue("核验状态");
                row.CreateCell(9).SetCellValue("是否满足双通道");
                row.CreateCell(10).SetCellValue("核验时间");
                row.CreateCell(11).SetCellValue("核验人");


                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "cs")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hyzt")
                            {
                                if (kvp.Value!="" && kvp.Value!= null )
                                {
                                    if (kvp.Value.ToString().Equals("1"))
                                    {
                                        row.CreateCell(8).SetCellValue("已核验");
                                    }
                                }
                                else
                                {
                                    row.CreateCell(8).SetCellValue("未核验");
                                }
                              
                            }
                            else if (kvp.Key == "jsrysstd")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "yh_create_on")
                            {
                                row.CreateCell(10).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "nickname")
                            {
                                row.CreateCell(11).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                if (hyzt == "undefined")
                {
                    return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "任务明细" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
                }
                else
                {
                    return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", hyzt + "任务明细" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }



        /// <summary>
        /// 导出该任务下所有信息 其他不合格信息
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/task/taskExportbhg")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult taskExportbhg([FromQuery] string taskid, [FromQuery] string txtKeyWord, [FromQuery] string nickname)
        {
            try
            {
                var userNumber = this.CurrentUser();


                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);

                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
                string sql = "";


                string strWhere = "";

                if (!string.IsNullOrEmpty(txtKeyWord))
                {
                    strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
                }
                if (!string.IsNullOrEmpty(nickname))
                {
                    strWhere += " and (nickname  like '%" + nickname + "%')";
                }

                string year = DateTime.Now.Year.ToString();
                string sql_count = @$"select count(1) cnt from  zm_task t where 1=1  " + strWhere;
                sql = " SELECT ROW_NUMBER() OVER ()index,xzq,town,village,fwbh,address,hzxm,CONCAT_WS(',', NULLIF(x1, ''), NULLIF(x2, ''),  NULLIF(x3, ''), NULLIF(x4, ''), NULLIF(x5, '')) as risk_items,create_name,create_on  " +
                    "FROM (    SELECT          t1.fwbh,t2.xzq,t2.towndm town,t2.villagedm village,  t2.address,t2.hzxm, t2.create_name,t2.create_on,  CASE WHEN t2.wdwhjgfjgqsyyrkrgbdj = 1   THEN '存在彩钢板建筑出租住人'  END x1,       CASE WHEN t3.sfczckfhwggptzl = '1'  THEN '存在窗口防护网、广告牌、铁栅栏'  END x2,        CASE WHEN t3.use_electric_bicycle = 1   THEN '电动自行车及蓄电池在室内停放充电'  END x3,        CASE WHEN t3.use_bottled_liquefied_gas = 1  THEN '违规使用液化石油气瓶'  END x4,        CASE WHEN t3.use_stacked_items = '1'   THEN '通道堆放杂物'  END x5    FROM (SELECT * FROM zm_taskdetail WHERE  year='" + year + "' AND taskid='" + taskid + "') t1   " +
                    " LEFT JOIN zm_pc_baselog_sqjz t2 ON t1.fwbh = t2.fwbh         AND t1.baseid = t2.ID         AND t1.ybhnumber = t2.create_by    " +
                    "LEFT JOIN zm_pc_yhlog_sqjz t3 ON t1.fwbh = t3.fwbh         AND t1.baseid = t3.base_id         AND t1.ybhnumber = t3.create_by) t WHERE (x1 IS NOT NULL     OR x2 IS NOT NULL     OR x3 IS NOT NULL     OR x4 IS NOT NULL     OR x5 IS NOT NULL) " + strWhere;


                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();

                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("不合格明细");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("房屋编号");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("户主姓名");
                row.CreateCell(7).SetCellValue("隐患项");
                row.CreateCell(8).SetCellValue("核验人");
                row.CreateCell(9).SetCellValue("核验时间");



                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "risk_items")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "create_name")
                            {
                                row.CreateCell(8).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "create_on")
                            {
                                row.CreateCell(9).SetCellValue(kvp.Value + "");
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",  "其他隐患不合格明细" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
             
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// 导出该任务下所有信息
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/task/taskExportALL")]
        [Produces("application/octet-stream")]
        public IActionResult taskExportALL([FromQuery] string startTime, [FromQuery] string endTime,[FromQuery] string pch, [FromQuery] string cked)
        {
            try
            {
                var userNumber = this.CurrentUser();


                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);

                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
                string sql = "";


                string strWhere = " ";
                string str2 = " where 1=1  ";
                if (model != null)
                {
                    if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                        str2 += " and xzqdm='" + model.RegionCode + "'";
                    }
                }



                string str = "";
          
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    strWhere += " and (create_on  between '" + startTime + "' and '" + endTime + " 23:59:59')";
                }
                if (!string.IsNullOrEmpty(pch))
                {
                    str = " and pch='" +pch+ "'";
                }
                string year = DateTime.Now.Year.ToString();

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }



                sql = @"select distinct(xzq) from zm_building_ledger "+ str2;
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                List<string> xzqlst = new List<string>();
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    var properties = obj as IDictionary<string, object>;
                    if (obj != null)
                    {
                        foreach (var kvp in properties)
                        {
                            // var val = kvp.Value.ToString();
                            if (kvp.Key == "xzq")
                            {
                               string xzq = kvp.Value + "";
                                if (!xzqlst.Contains(xzq))
                                {
                                   xzqlst.Add(xzq);
                                }
                            }
                        }
                    }
                }
                byte[] buffer = null;
                if (xzqlst.Count > 0)
                {
                    IWorkbook wb = new XSSFWorkbook();
                    for (int j = 0; j < xzqlst.Count; j++)
                    {
                        string xzq = xzqlst[j].ToString();
                        
                        sql = @"SELECT ROW_NUMBER( ) OVER ( ) INDEX,* FROM (
	SELECT T.*,M.create_on,M.create_name yh_create_name,M.jsrysstd,case when M.wdwhjgfjgqsyyrkrgbdj=1 then '是' when M.wdwhjgfjgqsyyrkrgbdj='0' then '否' end wdwhjgfjgqsyyrkrgbdj,CASE
			WHEN M.fwyfwdddzxccdsssfayqjshsf = 1 THEN
			'是' 
			WHEN M.fwyfwdddzxccdsssfayqjshsf = '0' THEN
			'否' 
			WHEN fwyfwdddzxccdsssfayqjshsf = '2' or fwyfwdddzxccdsssfayqjshsf is null  THEN
				'不涉及' 
		END fwyfwdddzxccdsssfayqjshsf,
	
		CASE
	WHEN tt.sfczckfhwggptzl = '1' THEN
	'是' 
	WHEN tt.sfczckfhwggptzl = '0' THEN
	'否' 
	END sfczckfhwggptzl,  
		
		CASE
	
	WHEN use_electric_bicycle = 1 THEN
	'是' 
	WHEN use_electric_bicycle = '0' THEN
	'否' 
	END use_electric_bicycle,  
		
		CASE
	
	WHEN use_bottled_liquefied_gas = 1 THEN
	'是' 
	WHEN use_bottled_liquefied_gas = '0' THEN
	'否' 
	END use_bottled_liquefied_gas, 

	
	CASE
	
	WHEN use_stacked_items = 1 THEN
	'是' 
	WHEN use_stacked_items = '0' THEN
	'否' 
	END use_stacked_items,
		
		
		CASE
	
	WHEN slljdx = '1' THEN
	'是' 
	WHEN slljdx = '0' THEN
	'否' 
	END slljdx, 
	CASE
	
	WHEN notes = '1' THEN
	'是' 
	WHEN notes = '0' or notes is null  THEN
	'否' 
	END notes   FROM (SELECT t1.fwbh,
			t1.taskid,
			t1.hyzt,
			t1.baseid,
			t1.ybhnumber,nickname,hzxm,address,t3.xzq,t3.town,t3.village,t3.xzqdm,t3.towndm,t3.villagedm,t1.cs,t1.pch 
		FROM (select * from zm_taskdetail where hyzt='1' " + str + " )  t1 LEFT JOIN zm_task_zuser t2 ON t1.number = t2.number LEFT JOIN zm_buildings t3 ON t1.fwbh = t3.fwbh) T LEFT JOIN zm_pc_baselog_sqjz M ON T.fwbh = M.fwbh  and T.baseid=M.id and T.ybhnumber=M.create_by  LEFT JOIN zm_pc_yhlog_sqjz tt ON T.fwbh = tt.fwbh   AND T.baseid = tt.base_id  AND T.ybhnumber = tt.create_by  ORDER BY M.create_on) T  where 1=1 " + strWhere + "  and fwbh not in (select fwbh from " + table_fwzt + " where final_create_on='" + endTime + " 23:59:59' and final_fwzt is not null )     and xzq='" + xzq + "'";

                        if (cked == "true")
                        {

                            sql = @"SELECT ROW_NUMBER( ) OVER ( ) INDEX,* FROM (
	SELECT T.*,M.create_on,M.create_name yh_create_name,M.jsrysstd,case when M.wdwhjgfjgqsyyrkrgbdj=1 then '是' when M.wdwhjgfjgqsyyrkrgbdj='0' then '否' end wdwhjgfjgqsyyrkrgbdj,CASE
			WHEN M.fwyfwdddzxccdsssfayqjshsf = 1 THEN
			'是' 
			WHEN M.fwyfwdddzxccdsssfayqjshsf = '0' THEN
			'否' 
			WHEN fwyfwdddzxccdsssfayqjshsf = '2' or fwyfwdddzxccdsssfayqjshsf is null  THEN
				'不涉及' 
		END fwyfwdddzxccdsssfayqjshsf,
	
		CASE
	WHEN tt.sfczckfhwggptzl = '1' THEN
	'是' 
	WHEN tt.sfczckfhwggptzl = '0' THEN
	'否' 
	END sfczckfhwggptzl,  
		
		CASE
	
	WHEN use_electric_bicycle = 1 THEN
	'是' 
	WHEN use_electric_bicycle = '0' THEN
	'否' 
	END use_electric_bicycle,  
		
		CASE
	
	WHEN use_bottled_liquefied_gas = 1 THEN
	'是' 
	WHEN use_bottled_liquefied_gas = '0' THEN
	'否' 
	END use_bottled_liquefied_gas, 

	
	CASE
	
	WHEN use_stacked_items = 1 THEN
	'是' 
	WHEN use_stacked_items = '0' THEN
	'否' 
	END use_stacked_items,
		
		
		CASE
	
	WHEN slljdx = '1' THEN
	'是' 
	WHEN slljdx = '0' THEN
	'否' 
	END slljdx, 
CASE
	
	WHEN notes = '1' THEN
	'是' 
	WHEN notes = '0' or notes is null  THEN
	'否' 
	END notes
	   FROM (SELECT t1.fwbh,
			t1.taskid,
			t1.hyzt,
			t1.baseid,
			t1.ybhnumber,nickname,hzxm,address,t3.xzq,t3.town,t3.village,t3.xzqdm,t3.towndm,t3.villagedm,t1.cs,t1.pch 
		FROM (select * from zm_taskdetail where hyzt='1' " + str + " )  t1 LEFT JOIN zm_task_zuser t2 ON t1.number = t2.number LEFT JOIN zm_buildings t3 ON t1.fwbh = t3.fwbh) T LEFT JOIN zm_pc_baselog_sqjz M ON T.fwbh = M.fwbh  and T.baseid=M.id and T.ybhnumber=M.create_by  LEFT JOIN zm_pc_yhlog_sqjz tt ON T.fwbh = tt.fwbh   AND T.baseid = tt.base_id  AND T.ybhnumber = tt.create_by  ORDER BY M.create_on) T  where 1=1 " + strWhere + "  and fwbh not in (select fwbh from " + table_fwzt + " where final_create_on='" + endTime + " 23:59:59' and final_fwzt is not null )     and xzq='" + xzq + "' and villagedm in (select villagedm from  zdcmd)";


                        }



                        data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();


                        ISheet sheet = wb.CreateSheet(xzq + "核验明细");
                        // 第一行 标题
                        IRow row = sheet.CreateRow(0);
                        row.CreateCell(0).SetCellValue("序号");
                        row.CreateCell(1).SetCellValue("区名称");
                        row.CreateCell(2).SetCellValue("街/乡镇");
                        row.CreateCell(3).SetCellValue("村/社区");
                        row.CreateCell(4).SetCellValue("村代码");

                        row.CreateCell(5).SetCellValue("房屋编号");
                        row.CreateCell(6).SetCellValue("房屋地址");
                        row.CreateCell(7).SetCellValue("房屋层数");
                        row.CreateCell(8).SetCellValue("户主姓名");


                        row.CreateCell(9).SetCellValue("核验状态");
                        row.CreateCell(10).SetCellValue("是否满足双通道");
                        row.CreateCell(11).SetCellValue("是否存在夹芯彩钢板出租住人");
                        //row.CreateCell(12).SetCellValue("服务于房屋的电动自行车充电设施是否按要求建设");
                        row.CreateCell(12).SetCellValue("是否存在窗口防护网、广告牌、铁栅栏");
                        row.CreateCell(13).SetCellValue("电动自行车及蓄电池是否在室内停放充电");
                        row.CreateCell(14).SetCellValue("是否违规使用液化石油气瓶");
                        row.CreateCell(15).SetCellValue("通道是否堆放杂物");
                        //row.CreateCell(17).SetCellValue("是否私拉乱接电线");
                        //row.CreateCell(18).SetCellValue("是否存在其他高风险火灾隐患问题");


                        row.CreateCell(16).SetCellValue("核验时间");
                        row.CreateCell(17).SetCellValue("核验人");
                        row.CreateCell(18).SetCellValue("轮次");
                        // 第二行 写数据
                        int i = 1;
                        foreach (object obj in data)
                        {
                            Dictionary<string, object> dictionary = new Dictionary<string, object>();
                            if (obj != null)
                            {

                                var properties = obj as IDictionary<string, object>;
                                row = sheet.CreateRow(i);
                                foreach (var kvp in properties)
                                {
                                    // var val = kvp.Value.ToString();
                                    if (kvp.Key == "index")
                                    {
                                        row.CreateCell(0).SetCellValue(i);
                                    }
                                    else if (kvp.Key == "xzq")
                                    {
                                        row.CreateCell(1).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "town")
                                    {
                                        row.CreateCell(2).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "village")
                                    {
                                        row.CreateCell(3).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "villagedm")
                                    {
                                        row.CreateCell(4).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "fwbh")
                                    {
                                        row.CreateCell(5).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "address")
                                    {
                                        row.CreateCell(6).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "cs")
                                    {
                                        row.CreateCell(7).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "hzxm")
                                    {
                                        row.CreateCell(8).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "hyzt")
                                    {
                                        if (kvp.Value != "" && kvp.Value != null)
                                        {
                                            if (kvp.Value.ToString().Equals("1"))
                                            {
                                                row.CreateCell(9).SetCellValue("已核验");
                                            }
                                        }
                                        else
                                        {
                                            row.CreateCell(9).SetCellValue("未核验");
                                        }

                                    }
                                    else if (kvp.Key == "jsrysstd")
                                    {
                                        row.CreateCell(10).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "wdwhjgfjgqsyyrkrgbdj")
                                    {
                                        row.CreateCell(11).SetCellValue(kvp.Value + "");
                                    }
                                    //else if (kvp.Key == "fwyfwdddzxccdsssfayqjshsf")
                                    //{
                                    //    row.CreateCell(12).SetCellValue(kvp.Value + "");
                                    //}

                                    else if (kvp.Key == "sfczckfhwggptzl")
                                    {
                                        row.CreateCell(12).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "use_electric_bicycle")
                                    {
                                        row.CreateCell(13).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "use_bottled_liquefied_gas")
                                    {
                                        row.CreateCell(14).SetCellValue(kvp.Value + "");
                                    }



                                    else if (kvp.Key == "use_stacked_items")
                                    {
                                        row.CreateCell(15).SetCellValue(kvp.Value + "");
                                    }
                                    //else if (kvp.Key == "slljdx")
                                    //{
                                    //    row.CreateCell(17).SetCellValue(kvp.Value + "");
                                    //}
                                    //else if (kvp.Key == "notes")
                                    //{
                                    //    row.CreateCell(18).SetCellValue(kvp.Value + "");
                                    //}

                                    else if (kvp.Key == "create_on")
                                    {
                                        row.CreateCell(16).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "nickname")
                                    {
                                        row.CreateCell(17).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "pch")
                                    {
                                        row.CreateCell(18).SetCellValue(kvp.Value + "");
                                    }

                                }

                                i++;
                            }
                        }
                        using (MemoryStream ms = new MemoryStream())
                        {
                            wb.Write(ms);
                            buffer = ms.ToArray();
                        }
                    }
                }
                else
                {
                    // 处理空列表情况
                    buffer = new byte[] { }; // 返回空文件
                }
                

                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", pch+startTime + "至"+endTime+"各区核验明细"+ ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }



        /// <summary>
        /// 导出该任务下所有信息
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("static/task/taskExportXZ")]
        [Produces("application/octet-stream")]
        public IActionResult taskExportXZ([FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string pch, [FromQuery] string cked)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string year = DateTime.Now.Year.ToString();


                string sql = @"select ROW_NUMBER() OVER ()index,t4.xzq,t1.xzqdm,COALESCE(t1.zcount,0) zcount,COALESCE(t1.yhycount,0) yhycount,COALESCE(t2.bhgcount,0) bhgcount,COALESCE(t3.xzcount,0) xzcount,COALESCE(t2.bhgcount-t3.xzcount,0)   sycount  from (select xzqdm,count(1) zcount,sum(case when hyzt=1 then 1 else 0 end) yhycount  from zm_taskdetail where pch= '" + pch + "'  and year = '" + year + "' GROUP BY xzqdm) t1" +
                " left join (select xzqdm,xzq,sum(case when jsrysstd='否' then 1 else 0 end) bhgcount from zm_pc_baselog_sqjz where id in (select baseid from zm_taskdetail where pch='" + pch + "'  and year='" + year + "' )   GROUP BY xzqdm,xzq) t2  on t1.xzqdm=t2.xzqdm" +
                 " left join (select xzqdm, xzq, sum(case when jsrysstd= '是' then 1 else 0 end) xzcount from(select DISTINCT ON (t.fwbh) t.fwbh, t.xzqdm, t.xzq, t.yhly, t.id, t.create_on, t.jsrysstd, t.create_name, t.cs from zm_pc_baselog_sqjz t  where fwbh in (select fwbh from zm_pc_baselog_sqjz where id in (select baseid from zm_taskdetail where pch= '" + pch + "'  and year = '" + year + "')  and jsrysstd = '否')   and yhly = 2   ORDER BY t.fwbh, t.create_on DESC) tt GROUP BY xzqdm, xzq) t3 on t1.xzqdm = t3.xzqdm" +
                 " left join zm_dic_xzq t4 on t1.xzqdm=t4.xzqdm";
               
                var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();

                IWorkbook wb = new XSSFWorkbook();

                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();


                        ISheet sheet = wb.CreateSheet("销账统计");
                        // 第一行 标题
                        IRow row = sheet.CreateRow(0);
                        row.CreateCell(0).SetCellValue("序号");
                        row.CreateCell(1).SetCellValue("区名称");
                        row.CreateCell(2).SetCellValue("行政区代码");
                        row.CreateCell(3).SetCellValue("抽检数量");
                        row.CreateCell(4).SetCellValue("已核验数量");

                        row.CreateCell(5).SetCellValue("双通道不合格数量");
                        row.CreateCell(6).SetCellValue("销账数量");
                        row.CreateCell(7).SetCellValue("未销账数量");
                        // 第二行 写数据
                        int i = 1;
                        foreach (object obj in data)
                        {
                            Dictionary<string, object> dictionary = new Dictionary<string, object>();
                            if (obj != null)
                            {

                                var properties = obj as IDictionary<string, object>;
                                row = sheet.CreateRow(i);
                                foreach (var kvp in properties)
                                {
                                    // var val = kvp.Value.ToString();
                                    if (kvp.Key == "index")
                                    {
                                        row.CreateCell(0).SetCellValue(i);
                                    }
                                    else if (kvp.Key == "xzq")
                                    {
                                        row.CreateCell(1).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "xzqdm")
                                    {
                                        row.CreateCell(2).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "zcount")
                                    {
                                        row.CreateCell(3).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "yhycount")
                                    {
                                        row.CreateCell(4).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "bhgcount")
                                    {
                                        row.CreateCell(5).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "xzcount")
                                    {
                                        row.CreateCell(6).SetCellValue(kvp.Value + "");
                                    }
                                    else if (kvp.Key == "sycount")
                                    {
                                        row.CreateCell(7).SetCellValue(kvp.Value + "");
                                    }
                                }

                                i++;
                            }
                        }
                       byte[] buffer = null;
                      using (MemoryStream ms = new MemoryStream())
                        {
                            wb.Write(ms);
                            buffer = ms.ToArray();
                        }
               


                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", pch + "抽检任务销账统计" + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }


    }


}

